问题的描述如下:给定数据库中的两列,每个列内的所有记录可以视为一个集合,如何求这两个集合的交集,差集等。示例:
table1中字段firstname
tom
kevin
john
steven
marry
anthony
table2中字段username
jack
tom
william
tom
marry
Thomas
两个列的交集是tom, marry。解决的方法是采用union和group by:
SELECT name
FROM (SELECT firstname as name FROM table1 union SELECT username as name FROM table2) as alltable
group by name havingcount(*) > 1;
两个列的交集的补集:
SELECT name
FROM (SELECT firstname as name FROM table1 unionSELECT username as name FROM table2) as alltable
group by name havingcount(*) = 1;
第一个列和第二个列的差集:
SELECT * FROM table1
WHERE firstname not in
(SELECT name
FROM (SELECT firstname as name FROM table1 unionSELECT username as name FROM table2) as alltable
group by name havingcount(*) > 1)
类似的可以求第二个列和第一个列的差集。如果一个集合是另一个集合的子集,情况会简单一点。如果希望包含重复的记录,使用union all.
大家可以自己考虑一下。当然这个肯定不是唯一的解决方法了,就算抛砖引玉了。
参考:http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx