左连接查询语句
表1 left join 表2 on 条件; 然后where,having,group等语句可以照常使用
以下例子用两次左连接进行匹配
select t1.tname,mres,t2.tname,matime from m left join t as t1 on t1.tid=m.hid left join t as t2 on t2.tid=m.gid;
mysql> set names gbk;
Query OK, 0 rows affected (0.02 sec)
mysql> use qq;
Database changed
mysql> create table m(
-> mid int not null primary key,
-> hid int ,
-> gid int ,
-> mres varchar(20),
-> matime date
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> create table t(
-> tid int ,
-> tname varchar(20)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into m
-> values
-> (1,1,2,'2:0','2006-05-21'),
-> (2,2,3,'1:2','2006-06-21'),
-> (3,3,1,'2:5','2006-05-25'),
-> (4,2,1,'3:2','2006-07-21');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t
-> values
-> (1,'国安'),
-> (2,'申花'),
-> (3,'公益联队');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select $ from m;
ERROR 1054 (42S22): Unknown column '$' in 'field list'
mysql> select * from m;
+-----+------+------+------+------------+
| mid | hid | gid | mres | matime |
+-----+------+------+------+------------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 |
| 3 | 3 | 1 | 2:5 | 2006-05-25 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 |
+-----+------+------+------+------------+
4 rows in set (0.03 sec)
mysql> #将所有队列序号改为名字
mysql> select t1.tname,mres,t2.tname,matime from
-> m left join t as t1 on t1.tid=m.hid
-> left join t as t2 on t2.tid=m.gid;
+----------+------+----------+------------+
| tname | mres | tname | matime |
+----------+------+----------+------------+
| 国安 | 2:0 | 申花 | 2006-05-21 |
| 申花 | 1:2 | 公益联队 | 2006-06-21 |
| 公益联队 | 2:5 | 国安 | 2006-05-25 |
| 申花 | 3:2 | 国安 | 2006-07-21 |
+----------+------+----------+------------+
4 rows in set (0.01 sec)