mysql取差集、交集、并集

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

weixin_34075268

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

阅读量493

点赞数

文章标签： java 数据库

本文介绍如何使用MySQL进行差集、交集及并集的操作方法。通过具体SQL语句示例，展示了如何从两个不同结果集中找出特定电话号码的集合运算结果。

mysql取差集、交集、并集

博客分类： Mysql数据库
 
需求：从两个不同的结果集（一个是子集，一个是父集），字段为电话号码phone_number,找出父集中缺少的电话号码，以明确用户身份。 
结合网上资料，整理sql如下： 

//mysql取差集 

Java代码  收藏代码
select num FROM  
(  
  
select u.code_sn as id,u.phone_number as num from t1 b  
left join t2 u  
on b.from_user=u.code_sn  
where b.to_account='1000' and b.json_info like '%09月份%'  
  
union all  
  
select u.code_sn as id,u.phone_number as num from t3 o  
left join t2 u  
on o.buyer_code_sn=u.code_sn  
where o.seller_account_code_sn='1000' and o.json_info like '%09月份%'   
  
)t3  
group by num having count(num)=1  


//mysql取交集 

Java代码  收藏代码
select num FROM  
(  
  
select u.code_sn as id,u.phone_number as num from t1 b  
left join t2 u  
on b.from_user=u.code_sn  
where b.to_account='1000' and b.json_info like '%09月份%'  
  
union all  
  
select u.code_sn as id,u.phone_number as num from t1 o  
left join t2 u  
on o.buyer_code_sn=u.code_sn  
where o.seller_account_code_sn='1000' and o.json_info like '%09月份%'   
  
)t3  
group by num having count(num)=2  


//mysql取并集 

Java代码  收藏代码
select num FROM  
(  
  
select u.code_sn as id,u.phone_number as num from t1 b  
left join t2 u  
on b.from_user=u.code_sn  
where b.to_account='1000' and b.json_info like '%09月份%'  
  
union all  
  
select u.code_sn as id,u.phone_number as num from t1 o  
left join t2 u  
on o.buyer_code_sn=u.code_sn  
where o.seller_account_code_sn='1000' and o.json_info like '%09月份%'   
  
)t3