sql多表查询

select * from A a,B b ，如果A有4条，B有2条，那么最后是 8条记录。

 

group by 的话，会把重复的过滤掉。

 

mysql> select * from people where id in('100001','100002','100003','100004')  ;
+--------+-------+-----+
| id     | name  | sex |
+--------+-------+-----+
| 100001 | 姓名1 |   1 |
| 100002 | 姓名2 |   0 |
| 100003 | 姓名3 |   1 |
| 100004 | 姓名4 |   0 |
+--------+-------+-----+
4 rows in set

mysql> select * from people where id in('100002','100003');
+--------+-------+-----+
| id     | name  | sex |
+--------+-------+-----+
| 100002 | 姓名2 |   0 |
| 100003 | 姓名3 |   1 |
+--------+-------+-----+
2 rows in set
 

mysql> select * from 

(select * from people where id in('100001','100002','100003','100004')  ) A ,
(select * from people where id in('100002','100003')  ) B ;
+--------+-------+-----+--------+-------+-----+
| id     | name  | sex | id     | name  | sex |
+--------+-------+-----+--------+-------+-----+
| 100001 | 姓名1 |   1 | 100002 | 姓名2 |   0 |
| 100001 | 姓名1 |   1 | 100003 | 姓名3 |   1 |
| 100002 | 姓名2 |   0 | 100002 | 姓名2 |   0 |
| 100002 | 姓名2 |   0 | 100003 | 姓名3 |   1 |
| 100003 | 姓名3 |   1 | 100002 | 姓名2 |   0 |
| 100003 | 姓名3 |   1 | 100003 | 姓名3 |   1 |
| 100004 | 姓名4 |   0 | 100002 | 姓名2 |   0 |
| 100004 | 姓名4 |   0 | 100003 | 姓名3 |   1 |
+--------+-------+-----+--------+-------+-----+
8 rows in set
 

二、
select * from 
(select * from people where id in('100001','100002','100003','100004')  ) A ,
(select * from people where id in('100002','100003')  ) B where A.id='100001'

与 下面效果同

select * from 
(select * from people where id in('100001','100002','100003','100004') and id='100001'  ) A ,
(select * from people where id in('100002','100003')  ) B 

 

 

mysql> select 
 A.*
-- count(*) 
from 

(select * from people where id in('100001','100002','100003','100004')  ) A ,

(select * from people where id in('100002','100003')  ) B  group by id;
+--------+-------+-----+
| id     | name  | sex |
+--------+-------+-----+
| 100001 | 姓名1 |   1 |
| 100002 | 姓名1 |   0 |
| 100003 | 姓名3 |   1 |
| 100004 | 姓名4 |   0 |
+--------+-------+-----+
4 rows in set
 

mysql> select 
 A.*
-- count(*) 
from 

(select * from people where id in('100001','100002','100003','100004')  ) A ,

(select * from people where id in('100002','100003')  ) B  group by id,name;
+--------+-------+-----+
| id     | name  | sex |
+--------+-------+-----+
| 100001 | 姓名1 |   1 |
| 100002 | 姓名1 |   0 |
| 100003 | 姓名3 |   1 |
| 100004 | 姓名4 |   0 |
+--------+-------+-----+
4 rows in set

mysql> 

 

mysql> select 
 A.*
-- count(*) 
from 

(select * from people where id in('100001','100002','100003','100004')  ) A ,

(select * from people where id in('100002','100003')  ) B  group by  name;
+--------+-------+-----+
| id     | name  | sex |
+--------+-------+-----+
| 100001 | 姓名1 |   1 |
| 100003 | 姓名3 |   1 |
| 100004 | 姓名4 |   0 |
+--------+-------+-----+
3 rows in set

mysql>