根据MySQL中其他两个列的值来匹配列的值
让我们首先创建一个表-
mysql> create table DemoTable774 ( Id int, FirstName varchar(100), MatchId int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable774 values(101,'Chris',104); mysql> insert into DemoTable774 values(102,'Adam',103); mysql> insert into DemoTable774 values(103,'Carol',102); mysql> insert into DemoTable774 values(104,'Bob',101);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable774;
这将产生以下输出-
+------+-----------+---------+ | Id | FirstName | MatchId | +------+-----------+---------+ | 101 | Chris | 104 | | 102 | Adam | 103 | | 103 | Carol | 102 | | 104 | Bob | 101 | +------+-----------+---------+ 4 rows in set (0.00 sec)
以下是根据ID和MatchID显示FirstName的查询-
mysql> select tbl1.FirstName, tbl2.FirstName from DemoTable774 AS tbl1 left join DemoTable774 AS tbl2 ON( tbl1.MatchId = tbl2.Id );
这将产生以下输出-
+-----------+-----------+ | FirstName | FirstName | +-----------+-----------+ | Bob | Chris | | Carol | Adam | | Adam | Carol | | Chris | Bob | +-----------+-----------+ 4 rows in set (0.03 sec)