mysql查询两表差集，mysql查询两表按某字段对比的差集数据，mysql查询两数据表的不同数据
a 表数据
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
+----+----+----+-----+ | id | aa | bb | cc | +----+----+----+-----+ | 1 | 22 | 30 | 44 | | 2 | 27 | 22 | 33 | | 3 | 0 | 40 | 0 | | 4 | 0 | 10 | 0 | | 5 | 11 | 0 | 44 | | 6 | 0 | 0 | 55 | | 7 | 0 | 0 | 99 | | 8 | 0 | 0 | 10 | | 9 | 0 | 0 | 101 | +----+----+----+-----+ |
b 表数据
|
0 1 2 3 4 5 6 7 8 9 |
+----+-------+----+ | id | dd | ee | +----+-------+----+ | 1 | 3万 | 33 | | 2 | 30万 | 44 | | 3 | 20万 | 11 | | 4 | 240万 | 22 | | 5 | haha | 55 | | 6 | 20万 | 66 | +----+-------+----+ |
对比a表的cc字段和b表的ee字段
|
0 1 2 3 4 5 6 7 8 9 10 |
mysql> select * from a where cc not in (select ee from b); +----+----+----+-----+ | id | aa | bb | cc | +----+----+----+-----+ | 3 | 0 | 40 | 0 | | 4 | 0 | 10 | 0 | | 7 | 0 | 0 | 99 | | 8 | 0 | 0 | 10 | | 9 | 0 | 0 | 101 | +----+----+----+-----+ 5 rows in set |
如果想分组去重数据，则如下：
|
0 1 2 3 4 5 6 7 8 |
mysql> select * from a where cc not in (select ee from b) group by cc; +----+----+----+-----+ | id | aa | bb | cc | +----+----+----+-----+ | 3 | 0 | 40 | 0 | | 8 | 0 | 0 | 10 | | 7 | 0 | 0 | 99 | | 9 | 0 | 0 | 101 | +----+----+----+-----+ |
所以，获取两表差集数据的SQL语句格式是：
|
0 |
SELECT * FROM a WHERE cc NOT IN (SELECT ee FROM b); |
但是获取两表差集的方法不是唯一的，分析如下：
使用左连接的方法，id2列为NULL的都是差集内容
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select a.*,b.id as id2,b.dd,b.ee from a left join b on a.cc=b.ee group by a.cc order by a.id; +----+----+----+-----+------+------+------+ | id | aa | bb | cc | id2 | dd | ee | +----+----+----+-----+------+------+------+ | 1 | 22 | 30 | 44 | 2 | 30万 | 44 | | 2 | 27 | 22 | 33 | 1 | 3万 | 33 | | 3 | 0 | 40 | 0 | NULL | NULL | NULL | | 6 | 0 | 0 | 55 | 5 | haha | 55 | | 7 | 0 | 0 | 99 | NULL | NULL | NULL | | 8 | 0 | 0 | 10 | NULL | NULL | NULL | | 9 | 0 | 0 | 101 | NULL | NULL | NULL | +----+----+----+-----+------+------+------+ 7 rows in set |
修改一下语句，增加个条件就可以了，如下：
|
0 1 2 3 4 5 6 7 8 9 |
mysql> select a.*,b.id as id2,b.dd,b.ee from a left join b on a.cc=b.ee where b.ee is null group by a.cc order by a.id; +----+----+----+-----+------+------+------+ | id | aa | bb | cc | id2 | dd | ee | +----+----+----+-----+------+------+------+ | 3 | 0 | 40 | 0 | NULL | NULL | NULL | | 7 | 0 | 0 | 99 | NULL | NULL | NULL | | 8 | 0 | 0 | 10 | NULL | NULL | NULL | | 9 | 0 | 0 | 101 | NULL | NULL | NULL | +----+----+----+-----+------+------+------+ 4 rows in set |
这样就完美了，两表的差集数据都显示出来了。
方法不用太多，简单高效易理解就好。
下面就可以继续愉快的玩耍了。
补充，分组后的记录条数统计：
|
0 |
select count(*) from (select count(*) from a left join b on a.cc=b.ee where b.ee is null group by a.cc order by a.id) t; |