3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
还是问sql语句!
大家看这里，我修正一下问题
表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
表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
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
SeerMi 2007-12-06
楼主说的不够清晰,如果表二有重复,是不是按照id做累加num ?
如果是,那么直接用表1 left join ( 表二根据id的group by num )
然后加上条件就可以了
如果是,那么直接用表1 left join ( 表二根据id的group by num )
然后加上条件就可以了
areswang 2007-12-06
顶楼上。
-狙击手- 2007-12-06
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 行）
*/fa_ge 2007-12-06
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 行）
*/
fa_ge 2007-12-06
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 行）
*/
shirley_yueli 2007-12-06
不大明白
tomyuansir 2007-12-06
看不懂意思
MAX-长老 2007-12-06
都不知道你要干嘛。
lei414 2007-12-06
大家来看那看
you_tube 2007-12-06
太花了帮顶
