请问合并、分组的有关问题

请教合并、分组的问题



sbxlh                               updatetime                          ResultInfo       

GSFT201402250003	2014-04-29 11:11:48.100	认证

GSFT201402250003	2014-04-29 11:26:47.970	离线

GSFT201402250003	2014-04-29 15:22:44.263	认证

GSFT201402250003	2014-04-29 15:32:44.540	认证

GSFT201402250003	2014-04-29 16:07:43.670	认证

GSFT201402250003	2014-04-29 19:27:43.790	离线

逻辑就是根据认证和离线进行合并，一个认证、离线算一组数据
也就是得到以下数据
sbxlh                                  stime                                         etime
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

------解决思路----------------------

;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

)

,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='离线'

------解决思路----------------------



USE tempdb

GO

CREATE TABLE #TB

(

	sbxlh VARCHAR(50),

	updatetime DATETIME,

	ResultInfo NVARCHAR(50)

)

INSERT INTO #TB

        ( sbxlh, updatetime, ResultInfo )

VALUES  

('GSFT201402250003','2014-04-29 11:11:48.100','认证'),

('GSFT201402250003','2014-04-29 11:26:47.970','离线'),

('GSFT201402250003','2014-04-29 15:22:44.263','认证'),

('GSFT201402250003','2014-04-29 15:32:44.540','认证'),

('GSFT201402250003','2014-04-29 16:07:43.670','认证'),

('GSFT201402250003','2014-04-29 19:27:43.790','离线'),

('GSFT201402250003','2014-04-29 20:07:43.670','认证'),

('GSFT201402250003','2014-04-29 20:27:43.790','离线')



;WITH CTE AS

(

SELECT *,

		ROW_NUMBER() OVER (PARTITION BY sbxlh ORDER BY updatetime) AS row1,

		ROW_NUMBER() OVER (ORDER BY updatetime) - ROW_NUMBER() OVER (PARTITION BY sbxlh,ResultInfo ORDER BY updatetime) AS row2

		 FROM #TB

)

SELECT a.sbxlh,a.updatetime,b.updatetime

	FROM (SELECT sbxlh,MIN(updatetime) AS updatetime,row2+1 AS row2

			FROM CTE

				WHERE ResultInfo = '认证'

				GROUP BY sbxlh,row2) AS a

		LEFT JOIN (SELECT sbxlh,updatetime,ROW_NUMBER() OVER (ORDER BY updatetime) AS row2

						FROM cte

							WHERE ResultInfo = '离线') b ON a.sbxlh = b.sbxlh AND a.row2 = b.row2

请问合并、分组的有关问题

相关推荐