还是问sql语句!

[lei414]

大家看这里，我修正一下问题
表1
id code stock kc
1 11 3 4
2 22 4 19
3 33 4 5
4 44 7 2
5 55 9 7
表二
id num state
4 3 0
2 6 1
1 1 1
2 9 1
5 2 1
5 5 0
如何得到所有的 stock+num(state=0时候num算0) <> kc 的记录,就像
id code stock num kc

3 33 4 0 5
4 44 7 0 2
5 55 9 2 7

[you_tube]

太花了帮顶

[lei414]

大家来看那看

[MAX-长老]

都不知道你要干嘛。

[tomyuansir]

看不懂意思

[shirley_yueli]

不大明白

[fa_ge]

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,isnull(b.num,0)as num,a.kc from A a
left join
(
select id,sum(case when state=0 then 0 else num end)as num from B group by id
)b
on a.id=b.id
where a.stock+b.num<>a.kc
union all
select id,code,stock,0 as num,kc from A a
where not exists( select 1 from B where a.id=id)
and stock<>kc
order by a.id

/*
id          code        stock       num         kc          
----------- ----------- ----------- ----------- ----------- 
3           33          4           0           5
4           44          7           0           2
5           55          9           2           7

（所影响的行数为 3 行）
*/

[fa_ge]

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,isnull(b.num,0)as num,a.kc from A a
left join
(
select id,sum(case when state=0 then 0 else num end)as num from B group by id
)b
on a.id=b.id
where a.stock+isnull(b.num,0)<>a.kc

/*
id          code        stock       num         kc          
----------- ----------- ----------- ----------- ----------- 
3           33          4           0           5
4           44          7           0           2
5           55          9           2           7

（所影响的行数为 3 行）
*/

[-狙击手-]

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.num
from a
left join (select id,sum(case when state =0 then 0 else num end) as num from b group by id) bb
on 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           NULL
4           44          7           2           0
5           55          9           7           2

（所影响的行数为 3 行）
*/

[areswang]

顶楼上。

[SeerMi]

楼主说的不够清晰,如果表二有重复,是不是按照id做累加num ?

如果是,那么直接用表1 left join ( 表二根据id的group by num )
然后加上条件就可以了