表1
id code stock kc
1 11 3 4
2 22 4 19
3 33 5 5
4 44 7 2
5 55 9 7
表二
id num
4 3
2 6
1 1
2 9
5 2
5 5
如何得到所有的 stock+num<>kc的记录,就像
id code stock num kc
4 44 7 0 2
5 55 9 7 7
------解决方案--------------------
- Java code
[code=SQL]create table A(id int, code int, stock int, kc int)insert into A values(1, 11 , 3 , 4 )insert into A values(2, 22 , 4 , 19) insert into A values(3, 33 , 4 , 5 )insert into A values(4, 44 , 7 , 2 )insert into A values(5, 55 , 9 , 7 )create table B(id int, num int,state int)insert into B values(4, 3 ,0 )insert into B values(2, 6 ,1)insert into B values(1, 1 ,1)insert into B values(2, 9 ,1)insert into B values(5, 2 ,1)insert into B values(5, 5 ,0)select a.id,a.code,a.stock,a.kc,bb.numfrom aleft join (select id,sum(case when state =0 then 0 else num end) as num from b group by id) bbon a.id = bb.id where a.kc <> a.stock + isnull(bb.num,0)drop table a,b/*id code stock kc num ----------- ----------- ----------- ----------- ----------- 3 33 4 5 NULL4 44 7 2 05 55 9 7 2（所影响的行数为 3 行）*/