少记录变成多条记录问题，用一条SQL语句应该怎么写？

[Bob]

有表tbl
日期 收入 支出
2004-02-11 00:00:00 60 45
2004-03-01 00:00:00 60 45
2004-03-02 00:00:00 40 50
2004-03-05 00:00:00 50 40

/*
测试数据：
Create Table tbl([日期] smalldatetime,[收入] int ,[支出] int)

Insert Into tbl
select '2004-02-11', 60, 45
union select '2004-03-01',60, 45
union select '2004-03-02',40, 50
union select '2004-03-05',50, 40
*/

要得到的结果：
日期 收入 支出 余额
2004-02-01 00:00:00 NULL NULL NULL
2004-02-02 00:00:00 NULL NULL NULL
2004-02-03 00:00:00 NULL NULL NULL
2004-02-04 00:00:00 NULL NULL NULL
2004-02-05 00:00:00 NULL NULL NULL
2004-02-06 00:00:00 NULL NULL NULL
2004-02-07 00:00:00 NULL NULL NULL
2004-02-08 00:00:00 NULL NULL NULL
2004-02-09 00:00:00 NULL NULL NULL
2004-02-10 00:00:00 NULL NULL NULL
2004-02-11 00:00:00 60 45 15
2004-02-12 00:00:00 NULL NULL 15
2004-02-13 00:00:00 NULL NULL 15
2004-02-14 00:00:00 NULL NULL 15
2004-02-15 00:00:00 NULL NULL 15
2004-02-16 00:00:00 NULL NULL 15
2004-02-17 00:00:00 NULL NULL 15
2004-02-18 00:00:00 NULL NULL 15
2004-02-19 00:00:00 NULL NULL 15
2004-02-20 00:00:00 NULL NULL 15
2004-02-21 00:00:00 NULL NULL 15
2004-02-22 00:00:00 NULL NULL 15
2004-02-23 00:00:00 NULL NULL 15
2004-02-24 00:00:00 NULL NULL 15
2004-02-25 00:00:00 NULL NULL 15
2004-02-26 00:00:00 NULL NULL 15
2004-02-27 00:00:00 NULL NULL 15
2004-02-28 00:00:00 NULL NULL 15
2004-02-29 00:00:00 NULL NULL 15
2004-03-01 00:00:00 60 45 30
2004-03-02 00:00:00 40 50 20
2004-03-03 00:00:00 NULL NULL 20
2004-03-04 00:00:00 NULL NULL 20
2004-03-05 00:00:00 50 40 30
2004-03-06 00:00:00 NULL NULL 30
2004-03-07 00:00:00 NULL NULL 30
2004-03-08 00:00:00 NULL NULL 30
2004-03-09 00:00:00 NULL NULL 30
2004-03-10 00:00:00 NULL NULL 30
2004-03-11 00:00:00 NULL NULL 30
2004-03-12 00:00:00 NULL NULL 30
2004-03-13 00:00:00 NULL NULL 30
2004-03-14 00:00:00 NULL NULL 30
2004-03-15 00:00:00 NULL NULL 30
2004-03-16 00:00:00 NULL NULL 30
2004-03-17 00:00:00 NULL NULL 30
2004-03-18 00:00:00 NULL NULL 30
2004-03-19 00:00:00 NULL NULL 30
2004-03-20 00:00:00 NULL NULL 30
2004-03-21 00:00:00 NULL NULL 30
2004-03-22 00:00:00 NULL NULL 30
2004-03-23 00:00:00 NULL NULL 30
2004-03-24 00:00:00 NULL NULL 30
2004-03-25 00:00:00 NULL NULL 30
2004-03-26 00:00:00 NULL NULL 30
2004-03-27 00:00:00 NULL NULL 30
2004-03-28 00:00:00 NULL NULL 30
2004-03-29 00:00:00 NULL NULL 30
2004-03-30 00:00:00 NULL NULL 30
2004-03-31 00:00:00 NULL NULL 30

用一条sql语句怎么写？？


没有写过类似的SQL语句，是不是有什么规律？

[iamltd]

觉得用游标比较好

造个临时表，用函数把时间生成出来，再加个变量记录余额


用SQL语句就不清楚了

[xiaoyao19811201]

看不懂意思，哎，菜！
不过帮你顶。

[prcgolf]

UP

[8LY8Apollo]

--创建表
drop table test
create table test(日期 datetime ,收入 int,支出 int,余额 int)
--构建表数据
declare @a int ,@b int
select @a = 0,@b=0
while (@a<60)
begin
insert test(日期) select dateadd(day,@a,cast('2004-02-01' as datetime))
select @a = @a + 1
end
--你的测试表
drop table tb1
Create Table tb1(日期 smalldatetime,收入 int ,支出 int)

Insert Into tb1
select '2004-02-11', 60, 45
union select '2004-03-01',60, 45
union select '2004-03-02',40, 50
union select '2004-03-05',50, 40
go
--格式化表数据1
update a set a.收入=b.收入,a.支出=b.支出,a.余额=b.收入-b.支出 from test a join tb1 b on a.日期=b.日期
--格式化数据2
alter table test add fid int identity(1,1)
--select * from test
go
--格式化表数据3
declare @b int
select @b = 0
while (@b<60)
begin
update b set b.余额=isnull(a.余额,0)+isnull(b.余额,0) from test a , test b where b.fid=@b+1 and a.fid=@b
select @b = @b +1
end
go
--收尾工作
alter table test drop column fid
--结果
select * from test

[8LY8Apollo]

sorry,没看清楚，我再试试一条sql语句实现。

[LJWS]

/*
测试数据：
Create Table #t1([日期] smalldatetime,[收入] int ,[支出] int)

Insert Into #t1
select '2004-02-11', 60, 45
union select '2004-03-01',60, 45
union select '2004-03-02',40, 50
union select '2004-03-05',50, 40
*/
/*

declare @db datetime,@de datetime,@i int
select @db=min([日期])-(day(min([日期])))+1 from #t1
select @de=dateadd(day,-1,dateadd(month,1,max([日期])-day(max([日期]))+1)) from #t1
set @i=datediff(day,@db,@de)+1
set rowcount @i
select sn=identity(int,0,1) into #t from syscolumns a ,syscolumns b ,syscolumns c
select [日期]=@db+a.sn,[收入]=b.[收入],[支出]=b.[支出],[余额]=(select sum([收入])-sum([支出]) from #t1 where [日期]<=@db+a.sn)
from #t a left join #t1 b on datediff(day,@db+a.sn,b.[日期])=0

*/

[LJWS]

一条语句呀,高高,偶不会的说

[davorsuker39]

UP

[健者天行]

select Y.[日期],tbl.[收入],tbl.[支出],
(select sum(isNull(tbl.[收入],0)-Isnull(tbl.[支出],0)) from tbl where [日期] <= Y.[日期]) as [余额]

from tbl right join
(select dateadd(dd,N.i,dateadd(dd,1-day(m.MinDay),m.MinDay)) as [日期] from
(select 0 as i
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
union all select 18
union all select 19
union all select 20
union all select 21
union all select 22
union all select 23
union all select 24
union all select 25
union all select 26
union all select 27
union all select 28
union all select 29
union all select 30
union all select 31 ) N
,
(select min(日期) as MinDay from tbl group by datediff(month,0,日期)) M
where DateDiff(MM, dateadd(dd,N.i,dateadd(dd,1-day(m.MinDay),m.MinDay)) , M.MinDay)=0) as Y

on tbl.[日期]=Y.日期

[Bob]

问题已解决，如果有更高明的算法，该贴增加50分！

[qizhanfeng]

好像不行
LJWS(归来) ( 的就挺好

[Bob]

高手快出马：）