一句简单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)