declare @t table(时间 datetime, 收入 varchar(10), 付出 varchar(10),余额 varchar(10))
insert @t select '2009-03-31' , 23, '', 23
insert @t select '2009-03-31' , '', 100, -77
insert @t select '2009-03-31' , 30, '', -47
insert @t select '2009-04-01' , 40 , '', -7
insert @t select '2009-04-01' , '' , 50, -57 select id=identity(int,1,1),* into # from @t
select * from (select top 1 时间,'' 收入,'' 付出,余额 from # where datediff(dd,时间,'2009-04-01')=1 order by id desc) t
union all
select 时间,收入,付出,余额 from # where 时间='2009-04-01'
/*
时间 收入 付出 余额
----------------------- ---------- ---------- ----------
2009-03-31 00:00:00.000 -47
2009-04-01 00:00:00.000 40 -7
2009-04-01 00:00:00.000 50 -57(3 行受影响)
*/
drop table #
insert @t select '2009-03-31' , 23, '', 23
insert @t select '2009-03-31' , '', 100, -77
insert @t select '2009-03-31' , 30, '', -47
insert @t select '2009-04-01' , 40 , '', -7
insert @t select '2009-04-01' , '' , 50, -57 select id=identity(int,1,1),* into # from @t
select * from (select top 1 时间,'' 收入,'' 付出,余额 from # where datediff(dd,时间,'2009-04-01')=1 order by id desc) t
union all
select 时间,收入,付出,余额 from # where 时间='2009-04-01'
/*
时间 收入 付出 余额
----------------------- ---------- ---------- ----------
2009-03-31 00:00:00.000 -47
2009-04-01 00:00:00.000 40 -7
2009-04-01 00:00:00.000 50 -57(3 行受影响)
*/
drop table #
有的话declare @dateTime Datetime
set @dateTime='2009-04-01' --也可以是外面传递进来的参数
create table #temp(时间 datetime,收入 int,付出 int,余额 int)
insert into #temp
select top 1 @dateTime,null as 收入, null as 付出,余额 from 表 where 时间<@dateTime order by ID desc
insert into #temp
select 时间,收入,付出,余额 from 表 where 时间>=@datetimeselect * from #temp
如果没有自增的话,第一条记录可以这么取:去2009-4-1第一条的 余额-ISNULL(收入,0)+ISNULL(付出,0)