SQL 查询问题好难

[lgq_liang]

测试数据：
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