怎样查表A中n_ljje(累计金额)与表B中n_je(金额)的合计数不相等的记录?A表与B表的关联字段是c_hydm(会员代码),结果只要A.n_ljje,sum(B.n_je),A.c_hydm就可以了select a.n_ljje,c.n_je,a.c_hydm from a,(select c_hydm,sum(n_je) as n_je from b group by c_hydm)c where a.c_hydm = c.c_hydm and a.n_ljje <> c.n_je
select A.c_hydm,A.n_ljje,B.n_JE from A,(select c_hydm,n_JE = sum(n_JE) from B group by c_hydm)B where A.c_hydm = B.c_hydm and A.n_ljje <> n_je
Select A.c_hydm,A.n_ljje,SUM(B.n_je) As n_je From A Inner Join B On A.c_hydm=B.c_hydm Group By A.c_hydm,A.n_ljje Having A.n_ljje<>SUM(B.n_je)這個更好點
Create Table A (c_hydm Int, n_ljje Int) Create Table B (c_hydm Int, n_je Int) Insert A Select 1,30 Union All Select 2,20Insert B Select 1,10 Union All Select 1,20 Union All Select 2,10 Union All Select 2,5 GO Select A.c_hydm,A.n_ljje,SUM(B.n_je) As n_je From A Inner Join B On A.c_hydm=B.c_hydm Group By A.c_hydm,A.n_ljje Having A.n_ljje<>SUM(B.n_je) GO Drop Table A,B --Result /* c_hydm n_ljje n_je 2 20 15 */
select a.c_hydm,a.n_ljje,sum(b.n_je) from a inner join b on a.c_hydm=b.hydm group by a.c_hydm,a.n_ljje having a.n_ljje<>sum(b.n_je)
from
a,(select c_hydm,sum(n_je) as n_je from b group by c_hydm)c
where a.c_hydm = c.c_hydm and a.n_ljje <> c.n_je
from A,(select c_hydm,n_JE = sum(n_JE) from B group by c_hydm)B
where A.c_hydm = B.c_hydm and A.n_ljje <> n_je
A.c_hydm,A.n_ljje,SUM(B.n_je) As n_je From A Inner Join B On A.c_hydm=B.c_hydm
Group By A.c_hydm,A.n_ljje
Having A.n_ljje<>SUM(B.n_je)這個更好點
(c_hydm Int,
n_ljje Int)
Create Table B
(c_hydm Int,
n_je Int)
Insert A Select 1,30
Union All Select 2,20Insert B Select 1,10
Union All Select 1,20
Union All Select 2,10
Union All Select 2,5
GO
Select
A.c_hydm,A.n_ljje,SUM(B.n_je) As n_je From A Inner Join B On A.c_hydm=B.c_hydm
Group By A.c_hydm,A.n_ljje
Having A.n_ljje<>SUM(B.n_je)
GO
Drop Table A,B
--Result
/*
c_hydm n_ljje n_je
2 20 15
*/
from a inner join b on a.c_hydm=b.hydm
group by a.c_hydm,a.n_ljje
having a.n_ljje<>sum(b.n_je)