--创建表 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
sorry,没看清楚,我再试试一条sql语句实现。
/* 测试数据: 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*/
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 Yon tbl.[日期]=Y.日期
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
测试数据:
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*/
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 Yon tbl.[日期]=Y.日期
LJWS(归来) ( 的就挺好