有两个表
表A 表B
ID PN ID PN
1 A 1 B
2 A 2 A
3 B 3 D
4 C 4 D
5 B 5 A
6 E
怎么select出来这样的结果
A比B增加 A比B减少
E D
C数据项大概有5K左右
表A 表B
ID PN ID PN
1 A 1 B
2 A 2 A
3 B 3 D
4 C 4 D
5 B 5 A
6 E
怎么select出来这样的结果
A比B增加 A比B减少
E D
C数据项大概有5K左右
A比B增加:
select distinct PN from A where not exists(select 1 from B where PN=A.PN)A比B减少:
select distinct PN from B where not exists(select 1 from A where PN=B.PN)
select distinct pn from a where pn not in (select distinct pn from b)
A比B少的.
select distinct pn from b where pn not in (select distinct pn from a)
select a.pn from a left join b on a.pn=b.pn where b.pn is null
2.
select b.pn from a right join b on a.pn=b.pn where a.pn is null
insert 表A
select 1,'A'
union select 2,'A'
union select 3,'B'
union select 4,'C'
union select 5,'B'
union select 6,'E'
create table 表B(ID int,PN char(1))
insert 表B
select 1,'B'
union select 2,'A'
union select 3,'D'
union select 4,'D'
union select 5,'A'
select A比B增加=case when b.PN is null then a.PN end ,A比B减少=case when a.PN is null then b.PN end
from (select distinct PN from 表A) a full join (select distinct PN from 表B) b on a.PN=b.PN
/*
A比B增加 A比B减少
----- -----
NULL NULL
NULL NULL
C NULL
NULL D
E NULL(5 row(s) affected)
*/
select a.pn from a left join b on a.pn=b.pn where b.pn is null
2.
select b.pn from a right join b on a.pn=b.pn where a.pn is null