日期:2014-05-18  浏览次数:20528 次

如何通过SQL语句获取以下结果
有一个表T如下:
dt(datetime) command(varchar) ci(varchar) col col2
00:00:01.123 ... 0002223333 ... ....
00:00:02.223 start 0003233666 ... ....
00:00:03.245 ... 0001237332 ... ....
00:00:05.521 failed 0002334344 ... ....
00:00:06.223 ... 0005528832 ... ....
00:00:08.612 start 0001178333 ... ....
00:00:09.727 ... 0002466788 ... ....
00:00:11.113 complete 0005756890 ... ....
00:00:12.829 ... 0006666444 ... ....
00:00:13.326 ... NULL ... ....
00:00:14.223 start 0005528832 ... ....
00:00:15.612 start 0001178333 ... ....
00:00:16.727 ... 0002466788 ... ....
00:00:17.113 complete 0005756890 ... ....
00:00:18.829 failed 0006666444 ... ....
00:00:19.326 ... 0002334422 ... ....
......
说明:在dt列中时间是递增的且唯一,command列中start和failed/complete(互斥)是一对出现的,也就是出现一个start后面必定会出现一个falied或complete,现在要求找出两个start间中间只出现一次complete的记录(从第一个start开始到第二start结束,不包含第二个start),找出的数据就如上表中蓝色部分,如何用SQL实现?

注:如果表后面的一段数据是以start开始complete结尾也算是要找的数据,也就相当于查找前先在表的最后面添加一条虚拟的start记录

------解决方案--------------------
SQL code
改改

SELECT c.* 
FROM #T AS a
    INNER JOIN #T AS b ON a.command=b.command AND a.dt<b.dt AND b.dt=(SELECT min(dt) FROM #T WHERE command=a.command AND dt>a.dt)
    INNER JOIN #T AS c ON c.dt >= a.dt AND c.dt<b.dt
WHERE a.command='start' 
AND EXISTS(SELECT 1 FROM #T WHERE [dt] BETWEEN a.dt AND b.dt   HAVING count(CASE WHEN [command] IN(N'complete',N'failed') THEN 1 END)=1 AND COUNT(CASE WHEN [command]=N'complete' THEN 1 end)=1)

------解决方案--------------------
应为>10
SQL code
;with c1 as(
select *,rn=ROW_NUMBER()over(order by dt) from tb
),c2 as(
select *,dt as flg1,0 as flg2 from c1 where command='start'
union all
select b.*,a.flg1,(case when b.command='complete' then a.flg2+10 when b.command='failed' then 20 else a.flg2 end)
from c2 a inner join c1 b on b.rn=a.rn+1 and b.command<>'start'
)
select dt,command,ci,col,col2 from c2 a where exists(select 1 from c2 where flg1=a.flg1 and flg2=10)
and not exists(select 1 from c2 where flg1=a.flg1 and flg2>10)