如何在 MySQL 中使用 SELF JOIN?
mysqlmysqli database
要使用 SELF JOIN，让我们创建一个表。创建表的查询如下 −
mysql> create table SelfJoinDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CountryName varchar(20), -> CountryRank int, -> `Year` varchar(10) -> ); Query OK, 0 rows affected (1.02 sec)
现在，您可以使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('US',1,'2016');
Query OK, 1 row affected (0.12 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',5,'2013');
Query OK, 1 row affected (0.16 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('France',45,'2010');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Turkey',3,'2000');
Query OK, 1 row affected (0.17 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Japan',78,'1995');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Romania',110,'2007');
Query OK, 1 row affected (0.22 sec)
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',3,'2000');
Query OK, 1 row affected (0.16 sec)
使用 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)
这是 SELF JOIN 的查询 −
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
