楼主的测试数据都有点小问题,这个可以查询出来的create table kc ( id int identity(1,1), name varchar(10), --名称 status char(1), --状态 sl int, --数量 ye int default(0) --结余 default=0 )insert into kc(name,status,sl) select 'a','1',100 --入库100 insert into kc(name,status,sl) select 'a','0',20 --出库20 insert into kc(name,status,sl) select 'a','1',50 --入库50 insert into kc(name,status,sl) select 'a','0',30 --出库30 select * from kcselect id, name, status, sl, ( select sum(case when status=1 then sl else -sl end) from kc where id<=a.id ) as ye2 from kc adrop table kc
这个是更新的~~~create table kc ( id int identity(1,1), name varchar(10), --名称 status char(1), --状态 sl int, --数量 ye int default(0) --结余 default=0 )insert into kc(name,status,sl) select 'a','1',100 --入库100 insert into kc(name,status,sl) select 'a','0',20 --出库20 insert into kc(name,status,sl) select 'a','1',50 --入库50 insert into kc(name,status,sl) select 'a','0',30 --出库30 --select * from kc update kc set ye=(select sum(case when status=1 then sl else -sl end) from kc where id<=a.id) from kc a select * from kcdrop table kc
测试结果:1 a 1 100 100 2 a 0 20 80 3 a 1 50 130 4 a 0 30 100----------------------------1:入库100,结余100 2:出库20,结余 80 3:入库50,结余 130 4:出库30,结余 100没有问题啊!
id name status sl ye 1 a 1 100 100 2 a 0 20 80 3 a 1 50 130 4 a 0 100 30 这个是你上一帖的结果:ID=4的那列结余对吗?
怎样做才能把sl字段修改之后,所有的ye也跟着修改
id name status sl ye 1 a 1 100 100 2 a 0 20 80 3 a 1 50 130 4 a 0 100 30 这个是你上一帖的结果:ID=4的那列结余对吗? -------------------------1.入库100,结余100 2.出库20,结余80 3.入库50,结余130 4.出库100,结余30就是这样了. -----------------------
(
id int identity(1,1),
name varchar(10), --名称
status char(1), --状态
sl int, --数量
ye int default(0) --结余 default=0
)insert into kc(name,status,sl) select 'a','1',100 --入库100
insert into kc(name,status,sl) select 'a','0',20 --出库20
insert into kc(name,status,sl) select 'a','1',50 --入库50
insert into kc(name,status,sl) select 'a','0',30 --出库30
select * from kcselect
id,
name,
status,
sl,
(
select sum(case when status=1 then sl else -sl end)
from kc
where id<=a.id
) as ye2
from kc adrop table kc
(
id int identity(1,1),
name varchar(10), --名称
status char(1), --状态
sl int, --数量
ye int default(0) --结余 default=0
)insert into kc(name,status,sl) select 'a','1',100 --入库100
insert into kc(name,status,sl) select 'a','0',20 --出库20
insert into kc(name,status,sl) select 'a','1',50 --入库50
insert into kc(name,status,sl) select 'a','0',30 --出库30
--select * from kc update kc set ye=(select sum(case when status=1 then sl else -sl end) from kc where id<=a.id) from kc a
select * from kcdrop table kc
2 a 0 20 80
3 a 1 50 130
4 a 0 30 100----------------------------1:入库100,结余100
2:出库20,结余 80
3:入库50,结余 130
4:出库30,结余 100没有问题啊!
1 a 1 100 100
2 a 0 20 80
3 a 1 50 130
4 a 0 100 30 这个是你上一帖的结果:ID=4的那列结余对吗?
1 a 1 100 100
2 a 0 20 80
3 a 1 50 130
4 a 0 100 30 这个是你上一帖的结果:ID=4的那列结余对吗?
-------------------------1.入库100,结余100
2.出库20,结余80
3.入库50,结余130
4.出库100,结余30就是这样了.
-----------------------
YiZhiNet(我姓义很多人都觉得奇怪) (太感谢了.!!!!