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
这个到是个不错的选择
阅读(1706) | 评论(0) | 转发(0) |