求一按条件筛选行进行统计的SQL语句(有点难度)
表内容如下：
starttime status
2013-03-04 09:09:021
2013-03-04 15:01:155
2013-03-04 15:05:171
2013-03-04 15:28:015
2013-03-04 15:29:571
2013-03-04 15:34:365
我想查出所有status=1和其starttime的时间最相近的一条status=5的之间的时间长度。望高手帮忙想想
[解决办法]
如果你这格式1与5是一一按时间对应的话那就好办了
select a.starttime,b.starttime,DATEDIFF(D,a.starttime,b.starttime)
from (select starttime,row_number() over(order by starttime) rn from tb where status=1)a
left join
(select starttime,row_number() over(order by starttime) rn from tb where status=5)b
on a.rn=b.rn
[解决办法]
select a.starttime,b.starttime,DATEDIFF(D,a.starttime,b.starttime)
from (select starttime,
(select count(1) from tb where status=1 and starttime<=t1.starttime) rn from tb t1 where status=1)a
left join
(select starttime,
(select count(1) from tb where status=5 and starttime<=t1.starttime) rn from tb where status=5)b
on a.rn=b.rn
