Mysql求差集、并集、交集

最新推荐文章于 2024-04-18 19:00:05 发布

搬运工007

最新推荐文章于 2024-04-18 19:00:05 发布

阅读量578

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_42947952/article/details/128669543

版权

数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

Mysql求差集、并集、交集

1.差集

求两个集合的差集，mysql 中没有对应的关键字，可以进行合并两个集合，进行分组后如果个数大于 1，说明是重复的，如果个数为 1，说明是一个集合独自拥有的。

SELECT
 id
FROM
 (
  SELECT DISTINCT
   id
  FROM
   t_request_1
  WHERE
   id IN (
    SELECT
     id
    FROM
     t_request_2
    WHERE
     a > 0
    AND b BETWEEN '1'
    AND '2'
   )
  UNION ALL
   SELECT DISTINCT
    b.id
   FROM
    t_request_2 b
   WHERE
    feMoney > 0
   AND requestDate BETWEEN '20170909'
   AND '20171130'
 ) temp
GROUP BY
 id
HAVING
 COUNT(id) = 1
    
    
2.使用not in筛选出差集
SELECT * from task_detail_t WHERE business_types not in (SELECT DISTINCT business_types from file_send_detail_t );

3.使用LEFT OUTER join

select 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 null

    
4.使用EXCEPT

SELECT 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>25
    UNION ALL
    SELECT 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>25
    UNION
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a

2.通过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.id


3.union all

SELECT business_types from task_detail_t UNION all SELECT DISTINCT business_types from file_send_detail_t ;