导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

还是问sql语句!

lei414 2007-12-06 03:16:48
大家看这里,我修正一下问题
表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
...全文
47 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
SeerMi 2007-12-06
楼主说的不够清晰,如果表二有重复,是不是按照id做累加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
太花了帮顶
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告