求sql语句！

Question

       日期             出入库单号     入库   出库     结存  
2010-08-24 00:00:00.000 期初    NULL    NULL    30.000
2010-09-03 00:00:00.000 盘点    200.000 NULL  NULL
2010-09-04 00:00:00.000 IQ10100003 10.000 0.000 0.000
2010-09-05 00:00:00.000 盘点    100.000   NULL 0.000
2010-09-06 00:01:00.000 IQ10100007 10.000 0.000 0.000
2010-09-06 00:02:00.000 OT10100006 0.000 10.000 0.000
2010-09-06 00:03:00.000 IQ10100005 10.000 0.000 0.000
2010-09-07 00:00:00.000 IQ10100008 10.000 0.000 0.000
2010-09-07 00:00:00.000 盘点    40.000  NULL 0.000从上面的表中想得到下面的效果，请高手写个语句！
      日期             出入库单号     入库     出库      结存    盈亏
2010-08-24 00:00:00.000 期初                   30.000
2010-09-03 00:00:00.000 盘点       200.000         30.000  盈170.000
2010-09-04 00:00:00.000 IQ10100003   10.000         40.000
2010-09-05 00:00:00.000 盘点       100.000     40.000  盈60.000
2010-09-06 00:01:00.000 IQ10100007   10.000     50.000
2010-09-06 00:02:00.000 OT10100006       10.000 40.000
2010-09-06 00:03:00.000 IQ10100005   10.000         50.000
2010-09-07 00:00:00.000 IQ10100008   10.000         60.000
2010-09-07 00:00:00.000 盘点       40.000         60.000  亏20.000

Answer 1

       日期             出入库单号     入库   出库     结存  
2010-08-24 00:00:00.000 期初    NULL    NULL    30.000
2010-09-03 00:00:00.000 盘点    200.000 NULL  NULL
2010-09-04 00:00:00.000 IQ10100003 10.000 0.000 0.000
2010-09-05 00:00:00.000 盘点    100.000   NULL 0.000
2010-09-06 00:01:00.000 IQ10100007 10.000 0.000 0.000
2010-09-06 00:02:00.000 OT10100006 0.000 10.000 0.000
2010-09-06 00:03:00.000 IQ10100005 10.000 0.000 0.000
2010-09-07 00:00:00.000 IQ10100008 10.000 0.000 0.000
2010-09-07 00:00:00.000 盘点    40.000  NULL 0.000从上面的表中想得到下面的效果，请高手写个语句！
      日期             出入库单号     入库     出库      结存    盈亏
2010-08-24 00:00:00.000 期初                   30.000
2010-09-03 00:00:00.000 盘点       200.000         30.000  盈170.000
2010-09-04 00:00:00.000 IQ10100003   10.000         40.000
2010-09-05 00:00:00.000 盘点       100.000     40.000  盈60.000
2010-09-06 00:01:00.000 IQ10100007   10.000     50.000
2010-09-06 00:02:00.000 OT10100006       10.000 40.000
2010-09-06 00:03:00.000 IQ10100005   10.000         50.000
2010-09-07 00:00:00.000 IQ10100008   10.000         60.000
2010-09-07 00:00:00.000 盘点       40.000         60.000  亏20.000

Answer 2

select *,结存1=结存,盈亏=(isnull(入库,0)-isnull(出库,0)-结存1) from 
 (select *,结存1=(select sum(结存) from tb t where t.datetime1<tb.datetime1) from tb )
-- 呆会再亲测

Answer 3

create table test
(日期 datetime,
 出入仓单号 nvarchar(20),
 入库 smallmoney,
 出库 smallmoney,
 结存 smallmoney,
 盈亏 nvarchar(20))insert into test (日期,出入仓单号,入库,出库,结存)
select '2010-08-24 00:00:00.000','期初',NULL,NULL,30.000 union all
select '2010-09-03 00:00:00.000','盘点',200.000,NULL,NULL union all
        select '2010-09-04 00:00:00.000','IQ10100003',10.000,0.000,0.000 union all
        select '2010-09-05 00:00:00.000','盘点',100.000,NULL,0.000 union all
select '2010-09-06 00:01:00.000','IQ10100007',10.000,0.000,0.000 union all
        select '2010-09-06 00:02:00.000','OT10100006',0.000,10.000,0.000 union all
select '2010-09-06 00:03:00.000','IQ10100005',10.000,0.000,0.000 union all
        select '2010-09-07 00:00:00.000','IQ10100008',10.000,0.000,0.000 union all
        select '2010-09-07 00:00:00.000','盘点',40.000,NULL,0.000declare @count smallmoney,@num nvarchar(20),@date datetime
select * into test02 from test where 出入仓单号 = '期初'   ---构造与test一样结构的表,这里应该可以再判断一下数据库里面是否存在这个表，这边只是做了不存在表test02的情况。
set select @count = 结存 from test where 出入仓单号 = '期初'declare test01 cursor  
for select 出入仓单号,日期 from test where 出入仓单号 <> '期初' order by 日期
open test01
fetch next from test01 into @num,@date
while @@fetch_status = 0
begin
if @num = '盘点'
begin 
insert into test02 
select 日期,出入仓单号,isnull(入库,0),isnull(出库,0),结存 = @count,
盈亏 = case when 入库>@count then  '盈'+ cast((isnull(入库,0)-@count) as nvarchar(20))
else '亏' + cast((@count-isnull(入库,0)) as nvarchar(20)) end
from test
where 出入仓单号 = @num and 日期 = @date

end
else
begin
insert into test02 
select 日期,出入仓单号,isnull(入库,0),isnull(出库,0),结存 = @count + isnull(入库,0) - isnull(出库,0),null
from test
where 出入仓单号 = @num and 日期 = @date end
if exists (select * from test02 where 出入仓单号 = @num and 日期 = @date and 出入仓单号 <> '盘点')
begin
select @count = @count + isnull(入库,0) - isnull(出库,0) 
from test
where 出入仓单号 = @num and 日期 = @date
endfetch next from test01 into @num,@dateendclose test01
DEALLOCATE test01

Answer 4

--这里已经把结存正确算出
--呵呵，好辛苦啊！急转弯啊
/**
剩下得　update 盘点　行
再统计
看来不使用临时表不行了。还是使用临时表吧。公共表达式只用一次，我后面还要　排序　取值呢！
**/
create table #tb(日期 smalldatetime,出入库单号 nvarchar(50),入库 decimal(18,3),出库 decimal(18,3),结存 decimal(18,3) ) 
truncate table #tb
insert into #tb
select '2010-08-24 00:00:00.000','期初',NULL ,NULL, 30.000  
union select '2010-09-03 00:00:00.000','盘点',200.000 ,NULL, NULL
union select '2010-09-04 00:00:00.000','IQ10100003',10.000,0.000, 0.000
union select '2010-09-05 00:00:00.000','盘点',100.000, NULL,0.000
union select '2010-09-06 00:01:00.000','IQ10100007',10.000,0.000,0.000
union select '2010-09-06 00:02:00.000','OT10100006',0.000,10.000,0.000
union select '2010-09-06 00:03:00.000','IQ10100005',10.000, 0.000,0.000
union select '2010-09-07 00:00:00.000','IQ10100008',10.000, 0.000, 0.000
union select '2010-09-07 00:01:00.000','盘点',40.000 ,NULL,0.000
GO
;with cte as
(select *,(select SUM((ISNULL(a.结存,0)+ISNULL(a.入库,0)-ISNULL(a.出库,0))) from #tb a where a.出入库单号!=N'盘点'  and a.日期<#tb.日期) t1,(select top 1 日期 from #tb b where b.出入库单号!=N'盘点' and b.日期>#tb.日期) t2 
from #tb where 出入库单号!=N'盘点')--select *,结存1=isnull(a.t1,a.结存)+ISNULL(a.入库,0)-ISNULL(a.出库,0) from  cte a
update #tb set 结存=isnull(a.t1,a.结存)+ISNULL(a.入库,0)-ISNULL(a.出库,0) from  cte a inner join #tb on a.日期=#tb.日期 and a.出入库单号=#tb.出入库单号
select * from #tb
GO
日期                      出入库单号                                              入库                                      出库                                      结存
----------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2010-08-24 00:00:00     期初                                                 NULL                                    NULL                                    30.000
2010-09-03 00:00:00     盘点                                                 200.000                                 NULL                                    NULL
2010-09-04 00:00:00     IQ10100003                                         10.000                                  0.000                                   40.000
2010-09-05 00:00:00     盘点                                                 100.000                                 NULL                                    0.000
2010-09-06 00:01:00     IQ10100007                                         10.000                                  0.000                                   50.000
2010-09-06 00:02:00     OT10100006                                         0.000                                   10.000                                  40.000
2010-09-06 00:03:00     IQ10100005                                         10.000                                  0.000                                   50.000
2010-09-07 00:00:00     IQ10100008                                         10.000                                  0.000                                   60.000
2010-09-07 00:01:00     盘点                                                 40.000                                  NULL                                    0.000(9 行受影响)

Answer 5

--完成！呵呵！create table #tb(日期 smalldatetime,出入库单号 nvarchar(50),入库 decimal(18,3),出库 decimal(18,3),结存 decimal(18,3) ) 
truncate table #tb
insert into #tb
select '2010-08-24 00:00:00.000','期初',NULL ,NULL, 30.000  
union select '2010-09-03 00:00:00.000','盘点',200.000 ,NULL, NULL
union select '2010-09-04 00:00:00.000','IQ10100003',10.000,0.000, 0.000
union select '2010-09-05 00:00:00.000','盘点',100.000, NULL,0.000
union select '2010-09-06 00:01:00.000','IQ10100007',10.000,0.000,0.000
union select '2010-09-06 00:02:00.000','OT10100006',0.000,10.000,0.000
union select '2010-09-06 00:03:00.000','IQ10100005',10.000, 0.000,0.000
union select '2010-09-07 00:00:00.000','IQ10100008',10.000, 0.000, 0.000
union select '2010-09-07 00:01:00.000','盘点',40.000 ,NULL,0.000
GO/**
----------------------------  使用公共表达　式------------------
;with cte as
(select *,(select SUM((ISNULL(a.结存,0)+ISNULL(a.入库,0)-ISNULL(a.出库,0))) from #tb a where a.出入库单号!=N'盘点'  and a.日期<#tb.日期) t1,(select top 1 日期 from #tb b where b.出入库单号!=N'盘点' and b.日期>#tb.日期) t2 
from #tb where 出入库单号!=N'盘点')
--select *,结存1=isnull(a.t1,a.结存)+ISNULL(a.入库,0)-ISNULL(a.出库,0) from  cte a
update #tb set 结存=isnull(a.t1,a.结存)+ISNULL(a.入库,0)-ISNULL(a.出库,0) from  cte a inner join #tb on a.日期=#tb.日期 and a.出入库单号=#tb.出入库单号
----------------------------使用公共表达　式　------------------
**/select *,
(select SUM((ISNULL(a.结存,0)+ISNULL(a.入库,0)-ISNULL(a.出库,0))) from #tb a where a.出入库单号!=N'盘点'  and a.日期<#tb.日期) t1,
(select top 1 日期 from #tb b where b.出入库单号!=N'盘点' and b.日期>#tb.日期) t2 
 into #temp --创建一个临时表
from #tb where 出入库单号!=N'盘点'--更新非盘点的转存
update #tb set 结存=isnull(a.t1,a.结存)+ISNULL(a.入库,0)-ISNULL(a.出库,0) from  #temp  a inner join #tb on a.日期=#tb.日期 and a.出入库单号=#tb.出入库单号
GOupdate #tb set 结存=(select top 1 a.结存 from #tb a where  a.日期<#tb.日期 order by  a.日期 desc) from #tb where #tb.出入库单号=N'盘点'
--计算盘点GOselect *,盈亏=(case when 出入库单号=N'盘点'  then
                          case when(ISNULL(入库,0)-ISNULL(出库,0)-结存)>0 then '盈' else '亏' end+cast(ISNULL(入库,0)-ISNULL(出库,0)-结存  as varchar(10))
                else '' end ) from #tb
                日期                      出入库单号                                              入库                                      出库                                      结存                                      盈亏
----------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------
2010-08-24 00:00:00     期初                                                 NULL                                    NULL                                    30.000                                  
2010-09-03 00:00:00     盘点                                                 200.000                                 NULL                                    30.000                                  盈170.000
2010-09-04 00:00:00     IQ10100003                                         10.000                                  0.000                                   40.000                                  
2010-09-05 00:00:00     盘点                                                 100.000                                 NULL                                    40.000                                  盈60.000
2010-09-06 00:01:00     IQ10100007                                         10.000                                  0.000                                   50.000                                  
2010-09-06 00:02:00     OT10100006                                         0.000                                   10.000                                  40.000                                  
2010-09-06 00:03:00     IQ10100005                                         10.000                                  0.000                                   50.000                                  
2010-09-07 00:00:00     IQ10100008                                         10.000                                  0.000                                   60.000                                  
2010-09-07 00:01:00     盘点                                                 40.000                                  NULL                                    60.000                                  亏-20.000(9 行受影响)