如何在MySQL中使用SELF JOIN？
要使用SELFJOIN，让我们创建一个表。创建表的查询如下-
mysql> create table SelfJoinDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CountryName varchar(20), -> CountryRank int, -> `Year` varchar(10) -> );
现在，您可以使用insert命令在表中插入一些记录。查询如下-
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('US',1,'2016');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',5,'2013');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('France',45,'2010');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Turkey',3,'2000');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Japan',78,'1995');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Romania',110,'2007');
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',3,'2000');使用select语句显示表中的所有记录。查询如下-
mysql> select *from SelfJoinDemo;
以下是输出-
+----+-------------+-------------+------+ | Id | CountryName | CountryRank | Year | +----+-------------+-------------+------+ | 1 | US | 1 | 2016 | | 2 | UK | 5 | 2013 | | 3 | France | 45 | 2010 | | 4 | Turkey | 3 | 2000 | | 5 | Japan | 78 | 1995 | | 6 | Romania | 110 | 2007 | | 7 | UK | 3 | 2000 | +----+-------------+-------------+------+ 7 rows in set (0.00 sec)
这是SELFJOIN的查询-
mysql> SELECT DISTINCT t1.CountryName, t2.Year -> FROM SelfJoinDemo AS t1, -> SelfJoinDemo AS t2 -> WHERE t1.Year=t2.Year -> and t1.CountryName='US';
以下是输出-
+-------------+------+ | CountryName | Year | +-------------+------+ | US | 2016 | +-------------+------+ 1 row in set (0.00 sec