一句简单SQL语句~
A:
ID StuffID Cnt(件数)
1 1 2
2 2 4
3 3 2
4 4 5
5 5 3

B: (这个表要group by StuffID: select StuffID,count(1) as [件数] from B group by StuffID)
ID StuffID
1 1
2 1
3 3
4 4
5 4
6 4
7 4
8 4
9 6

2个表 A的StuffID为唯一索引. B的StuffID为任意不为空的原料

现在要显示2个表同种StuffID一样但件数不一样的记录:
A的ID A的StuffID B的StuffID A的Cnt B的件数
2 2 2 4 0
3 3 3 2 1
5 5 5 3 0
NULL NULL 6 0 1

[解决办法]
--try


select * from A
full join
(
select StuffID,count(1) as [件数] from B group by StuffID
)B on A.StuffID=B.StuffID
where A.Cnt <> B.件数

[解决办法]
create table A(ID int, StuffID int, Cnt int)
insert A select 1, 1, 2
union all select 2, 2, 4
union all select 3, 3, 2
union all select 4, 4, 5
union all select 5, 5, 3

create table B(ID int, StuffID int)
insert B select 1, 1
union all select 2, 1
union all select 3, 3
union all select 4, 4
union all select 5, 4
union all select 6, 4
union all select 7, 4
union all select 8, 4
union all select 9, 6

select A.ID as [A_ID], A.StuffID as [A_StuffID],B.StuffID as [B_StuffID],
isnull(A.Cnt,0) as [A_Cnt], isnull(B.件数,0) as [B_件数]
from A
full join
(
select StuffID,count(1) as [件数] from B group by StuffID
)B on A.StuffID=B.StuffID
where isnull(A.Cnt,0) <> isnull(B.件数,0)

--result
A_ID A_StuffID B_StuffID A_Cnt B_件数
----------- ----------- ----------- ----------- -----------
2 2 NULL 4 0
3 3 3 2 1
5 5 NULL 3 0
NULL NULL 6 0 1

(4 row(s) affected)