Mysql求差集、并集、交集

Mysql求差集、并集、交集

1.差集

求两个集合的差集，mysql 中没有对应的关键字，可以进行合并两个集合，进行分组后如果个数大于 1，说明是重复的，如果个数为 1，说明是一个集合独自拥有的。SELECTid
FROM(SELECT DISTINCTidFROMt_request_1WHEREid IN (SELECTidFROMt_request_2WHEREa > 0AND b BETWEEN '1'AND '2')UNION ALLSELECT DISTINCTb.idFROMt_request_2 bWHEREfeMoney > 0AND requestDate BETWEEN '20170909'AND '20171130') temp
GROUP BYid
HAVINGCOUNT(id) = 12.使用not in筛选出差集
SELECT * from task_detail_t WHERE business_types not in (SELECT DISTINCT business_types from file_send_detail_t );3.使用LEFT OUTER joinselect a.id,a.name,a.sex,a.age,b.school 
FROM 
(SELECT * FROM mike1.test001) a
LEFT OUTER join
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id
WHERE b.id IS NOT null4.使用EXCEPTSELECT business_types from task_detail_t EXCEPT (SELECT DISTINCT business_types from file_send_detail_t );

2.交集

下面的sql的意思是找到所有技术部年龄大于25的员工SELECT a.* FROM(SELECT id,code,name FROM test_emp WHERE age>25UNION ALLSELECT id,code,name FROM test_emp WHERE dept='JSB'
)a GROUP BY a.id HAVING COUNT(a.id)=2

3.并集

下面的sql的意思是找到所有技术部的员工和年龄大于30的员工union可以自动去除重复的内容,得到不重复的结果集SELECT a.* FROM(SELECT id,code,name FROM test_emp WHERE age>25UNIONSELECT id,code,name FROM test_emp WHERE dept='JSB'
)a2.通过id号相同，对表1和表2进行内连接，取相同的部分，不相同的部分省略掉。select a.*,b.school 
FROM 
(SELECT * FROM mike1.test001) a
inner JOIN
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id3.union allSELECT business_types from task_detail_t UNION all SELECT DISTINCT business_types from file_send_detail_t ;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！