;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY sbxlh ORDER BY updatetime)RN
,ROW_NUMBER()OVER(PARTITION BY sbxlh,ResultInfo ORDER BY updatetime)RN2
,*
FROM #TB
WHERE ResultInfo IN('认证','离线')
)
,CTE1 AS(
SELECT sbxlh,MIN(updatetime)updatetime
,MAX(RN)RN
FROM CTE
WHERE ResultInfo='认证'
GROUP BY sbxlh,RN-RN2
)
SELECT T1.sbxlh,T1.updatetime stime,T2.updatetime etime
FROM CTE1 T1
LEFT JOIN CTE T2 ON T1.sbxlh=T2.sbxlh
AND T1.RN+1=T2.RN AND T2.ResultInfo='离线'
SELECT ROW_NUMBER()OVER(PARTITION BY sbxlh ORDER BY updatetime)RN
,ROW_NUMBER()OVER(PARTITION BY sbxlh,ResultInfo ORDER BY updatetime)RN2
,*
FROM #TB
WHERE ResultInfo IN('认证','离线')
)
,CTE1 AS(
SELECT sbxlh,MIN(updatetime)updatetime
,MAX(RN)RN
FROM CTE
WHERE ResultInfo='认证'
GROUP BY sbxlh,RN-RN2
)
SELECT T1.sbxlh,T1.updatetime stime,T2.updatetime etime
FROM CTE1 T1
LEFT JOIN CTE T2 ON T1.sbxlh=T2.sbxlh
AND T1.RN+1=T2.RN AND T2.ResultInfo='离线'
with cte as (
select * ,(select min(updatetime) from #tb where updatetime > a.updatetime and ResultInfo = N'离线') as edtime
from #tb as a where ResultInfo = N'认证')select sbxlh , min(updatetime) as sttime , edtime from cte group by sbxlh , edtime/*
sbxlh sttime edtime
-------------------------------------------------- ----------------------- -----------------------
GSFT201402250003 2014-04-29 11:11:48.100 2014-04-29 11:26:47.970
GSFT201402250003 2014-04-29 15:22:44.263 2014-04-29 19:27:43.790
GSFT201402250003 2014-04-29 20:07:43.670 2014-04-29 20:27:43.790(3 row(s) affected)*/