测试数据：
t_p_baseinfo_id startdate sumdate
5183 2013-04-12 00:00:00.000 2013-04-12 00:00:00.000
5183 2013-04-13 00:00:00.000 2013-04-13 00:00:00.000
5183 2013-04-14 00:00:00.000 2013-04-14 00:00:00.000
5183 2013-04-15 00:00:00.000 2013-04-15 00:00:00.000
5183 2013-04-16 00:00:00.000 NULL
5183 2013-04-17 00:00:00.000 NULL
5183 2013-04-18 00:00:00.000 NULL
5183 2013-04-19 00:00:00.000 NULL
5183 2013-04-20 00:00:00.000 2013-04-20 00:00:00.000
5183 2013-04-21 00:00:00.000 NULL
5183 2013-04-22 00:00:00.000 NULL
5183 2013-04-23 00:00:00.000 NULL
生成结果如下：
t_p_baseinfo_id startdate sumdate
5183 2013/4/12 2013/4/12
5183 2013/4/13 2013/4/13
5183 2013/4/14 2013/4/14
5183 2013/4/15 2013/4/15
5183 2013/4/16 2013/4/15
5183 2013/4/17 2013/4/15
5183 2013/4/18 2013/4/15
5183 2013/4/19 2013/4/15
5183 2013/4/20 2013/4/20
5183 2013/4/21 2013/4/20
5183 2013/4/22 2013/4/20
5183 2013/4/23 2013/4/20
------解决思路----------------------
这个意思 ？
declare @t table(startdate datetime,sumdate datetime )
insert into @t select '2015-01-01 20:00:00' ,'2015-01-01 20:00:00'
insert into @t select '2015-01-02 21:00:00' ,null
insert into @t select '2015-01-03 23:00:00' ,'2015-01-03 20:00:00'
insert into @t select '2015-01-04 01:00:00' ,null
insert into @t select '2015-01-05 05:00:00' ,null
insert into @t select '2015-01-06 08:00:00' ,'2015-01-06 20:00:00'
insert into @t select '2015-01-07 15:00:00' ,null
insert into @t select '2015-01-08 08:00:00' ,null
;
with ta
as
(
select row_number()over(order by startdate) as orderid,* from @t)
select *,case when sumdate is null then (select max(sumdate) from ta where orderid < a.orderid ) else sumdate end
from ta a
orderid startdate sumdate
-------------------- ----------------------- ----------------------- -----------------------
1 2015-01-01 20:00:00.000 2015-01-01 20:00:00.000 2015-01-01 20:00:00.000
2 2015-01-02 21:00:00.000 NULL 2015-01-01 20:00:00.000
3 2015-01-03 23:00:00.000 2015-01-03 20:00:00.000 2015-01-03 20:00:00.000
4 2015-01-04 01:00:00.000 NULL 2015-01-03 20:00:00.000
5 2015-01-05 05:00:00.000 NULL 2015-01-03 20:00:00.000
6 2015-01-06 08:00:00.000 2015-01-06 20:00:00.000 2015-01-06 20:00:00.000
7 2015-01-07 15:00:00.000 NULL 2015-01-06 20:00:00.000
8 2015-01-08 08:00:00.000 NULL 2015-01-06 20:00:00.000