[b]需求:从两个不同的结果集(一个是子集,一个是父集),字段为电话号码phone_number,找出父集中缺少的电话号码,以明确用户身份。
结合网上资料,整理sql如下:[/b]
//[b]mysql取差集[/b]
//[b]mysql取交集[/b]
//[b]mysql取并集[/b]
结合网上资料,整理sql如下:[/b]
//[b]mysql取差集[/b]
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
//[b]mysql取交集[/b]
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
//[b]mysql取并集[/b]
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