mysql 查询差集方法

第一种是通过not in的方式去处理：

select id from table_a where id not in (select id from table_b);

第二种则是通过左连接(left join)的方式：

select * from table_a as a

left join table_b as b

on a.id = b.id

where a.id is NULL;

取AB表的差集，就是拿A表独有数据跟B表独有数据合并

那么思路就有了，直接通过union将两个查询的结果合并即可。

select * from A left join B on A.id = B.id where B.id is null

union

select * from A right join B on A.id = B.id where A.id is null;

同一个订单存在多条记录，需要过滤出失败的记录。就a集合中的失败的，与a集合中成功的差集

代码语言：javascript代码运行次数：0运行复制

select DISTINCT order_no from (
    SELECT order_no 
    FROM order_mqmsg
    WHERE notify_status != 1 and date( `create_time`) >= '2022-05-01' and date( `create_time`) <= '2022-05-31'
        GROUP BY `order_no`
) t1  WHERE t1.`order_no` not in (
    select DISTINCT order_no from (
    SELECT order_no 
    FROM order_mqmsg
    WHERE notify_status = 1 and date( `create_time`) >= '2022-05-01' and date( `create_time`) <= '2022-05-31'
            GROUP BY `order_no`
        ) t2 
)

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2022-06-23，如有侵权请联系 cloudcommunity@tencent 删除前往查看连接数据mysqlselect集合