三表以上查询

时间：2010-04-12 来源：ubuntuer

mysql> select * from a;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1 | kenthy | 24 |
| 2 | kenthy | 25 |
| 3 | jimmy | 25 |
+----+--------+-----+
3 rows in set (0.00 sec) mysql> select * from b;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1 | jimmy | 25 |
| 2 | kenthy | 25 |
| 3 | jimmy | 25 |
+----+--------+-----+
3 rows in set (0.01 sec) mysql> select * from c;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1 | jimmy | 25 |
| 2 | kenthy | 25 |
+----+--------+-----+
2 rows in set (0.01 sec) 那么要取得三个表的交集呢？？？？？ select a.* from a inner join (b,c) on(a.name=b.name and b.name=c.name and a.age=b.age and b.age=c.age and a.id=b.id and b.id=c.id);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 2 | kenthy | 25 |
+----+--------+-----+
shit这也太长了不是...
using好像不能三表using select * from a inner join (select b.* from b inner join c using(id,name,age)) as d using(id,name,age);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 2 | kenthy | 25 |
+----+--------+-----+
这个是不是好点了
当然如果你只有一个条件的话
SELECT * FROM a left join (b, c) ON (a.id=b.tid AND b.tid=c.tid) where a.id = 3
这个到是个不错的选择