结存台账语句

[qq_27341511]

SELECT DDATE,IQTYIN,IQTYOUT,IQTYBALANCE FROM TABLE1
日期 入库数 出库数 结存数

例如：
id, DDATE, IQTYIN,IQTYOUT,IQTYBALANCE
1 1-1 200
2 1-2 300
3 1-3 100
4 1-4 150
5 1-5 200

求IQTYBALANCE 语句

id, DDATE, IQTYIN,IQTYOUT,IQTYBALANCE
1 1-1 200 200
2 1-2 300 500
3 1-3 100 400
4 1-4 150 550
5 1-5 200 350

[不穿内裤的超人]

引用 2 楼 yupeigu 的回复:

[quote=引用 1 楼 qq_27341511 的回复:] 求解！ 谢谢

试试这个：

create table table1(id int,    DDATE varchar(10), IQTYIN int,IQTYOUT int ,IQTYBALANCE int)
go


insert into table1
select 1       ,'1-1',     200,null,null   union all  
select 2       ,'1-2',     300,null,null   union all
select 3       ,'1-3',     null,100,null   union all
select 4       ,'1-4',    150,null,null       union all
select 5       ,'1-5',    null,200,null
go

select id,ddate,
       IQTYIN,IQTYOUT,
	   (select sum(isnull(IQTYIN,0))-sum(isnull(IQTYOUT,0)) from table1 t2 where t1.id>=t2.id) as IQTYBALANCE
from table1 t1
/*
id	ddate	IQTYIN	IQTYOUT	IQTYBALANCE
1	1-1	200	NULL	200
2	1-2	300	NULL	500
3	1-3	NULL	100	400
4	1-4	150	NULL	550
5	1-5	NULL	200	350
*/

[/quote] 我测试了一下,居然可以,sql语句可以这样写也是,也真是高手

[qq_27341511]

求解！ 谢谢

[LongRui888]

引用 1 楼 qq_27341511 的回复:

求解！ 谢谢

试试这个：

create table table1(id int,    DDATE varchar(10), IQTYIN int,IQTYOUT int ,IQTYBALANCE int)
go


insert into table1
select 1       ,'1-1',     200,null,null   union all  
select 2       ,'1-2',     300,null,null   union all
select 3       ,'1-3',     null,100,null   union all
select 4       ,'1-4',    150,null,null       union all
select 5       ,'1-5',    null,200,null
go

select id,ddate,
       IQTYIN,IQTYOUT,
	   (select sum(isnull(IQTYIN,0))-sum(isnull(IQTYOUT,0)) from table1 t2 where t1.id>=t2.id) as IQTYBALANCE
from table1 t1
/*
id	ddate	IQTYIN	IQTYOUT	IQTYBALANCE
1	1-1	200	NULL	200
2	1-2	300	NULL	500
3	1-3	NULL	100	400
4	1-4	150	NULL	550
5	1-5	NULL	200	350
*/

[si12si12]

select *,(select sum(收入-支出) from 表 where 你的表的主键<=tem.你的表的主键) 本次余额 from 表 tem