有一个明细表与汇总表:
明细表MX
ID IDTYPE
---------------
1 A
2 B
3 A
4 C
汇总表HZ
IDTYPE CNT
---------------
A 3
B 1
C 0
D 1
怎么样取出HZ表与MX表中IDTYPE相同但明细表中ID汇总数与HZ表CNT不同的记录???
我知道可以写这样的语句查所有的:
select hz.idtype,hz.cnt,(select count(id) from mx where mx.idtype=hz.idtype) cnt2 from hz
但要精确查出想要的结果, 请问应怎么写语句???
正确的结果为:
IDTYPE CNT CNT2
A 3 2
C 0 1
D 1 0
------解决方案--------------------
- SQL code
select h.IDTYPE,h.CNT,m.CNT2 from HZ hfull outer join (select IDTYPE,count(*) as CNT2 from MX group by IDTYPE) on h.IDTYPE=m.IDTYPE and isnull(h.CNT,0)<>isnull(m.CNT2,0)
------解决方案--------------------
select A.IDTYPE , A.CNT, isnull(B.CNT2,0) as CNT2
from HZ A
left join
(select IDTYPE ,count(*) as CNT2
from MX
group by IDTYPE) B
on A.IDTYPE=B.IDTYPE and A.CNT<>isnull(B.CNT2,0)
------解决方案--------------------
select A.IDTYPE , A.CNT, isnull(B.CNT2,0) as CNT2
from HZ A
left join
(select IDTYPE ,count(*) as CNT2
from MX
group by IDTYPE) B
on A.IDTYPE=B.IDTYPE and A.CNT < >isnull(B.CNT2,0)