分段查询记录

2012-04-05

求助：分段查询记录有如下记录phonenumlogintimestatus153086150312011-09-04 00:01:51熄火153086150312011

求助：分段查询记录
有如下记录
phonenum logintime status
153086150312011-09-04 00:01:51熄火
153086150312011-09-04 00:01:52熄火
153086150312011-09-04 00:01:53停车
153086150312011-09-04 00:01:54停车
153086150312011-09-04 00:01:55熄火
153086150312011-09-04 00:01:56熄火
153086150322011-09-04 00:01:52停车
153086150322011-09-04 00:01:53停车
153086150322011-09-04 00:01:54熄火
153086150322011-09-04 00:01:55熄火

怎么样通过存储过程得到如下格式的结果啊

号码熄火开始时间熄火结束时间
15308615031 2011-09-04 00:01:51 2011-09-04 00:01:52
15308615031 2011-09-04 00:01:55 2011-09-04 00:01:56
15308615032 2011-09-04 00:01:54 2011-09-04 00:01:55

请高手指点啊

[解决办法]
为了思路清楚，用CTE写了个。。

SQL code

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TBGOCREATE TABLE TB(phonenum VARCHAR(50),logintime DATETIME,status VARCHAR(10))GOINSERT INTO TBSELECT '15308615031','2011-09-04 00:01:51','熄火' UNION ALLSELECT '15308615032','2011-09-04 00:01:54','熄火' UNION ALLSELECT '15308615031','2011-09-04 00:01:53','停车' UNION ALLSELECT '15308615031','2011-09-04 00:01:54','停车' UNION ALLSELECT '15308615031','2011-09-04 00:01:55','熄火' UNION ALLSELECT '15308615031','2011-09-04 00:01:56','熄火' UNION ALLSELECT '15308615032','2011-09-04 00:01:52','停车' UNION ALLSELECT '15308615032','2011-09-04 00:01:53','停车' UNION ALLSELECT '15308615031','2011-09-04 00:01:52','熄火' UNION ALLSELECT '15308615032','2011-09-04 00:01:55','熄火'GO;WITH MU AS (SELECT PHONENUM,LOGINTIME,STATUS,ROW_NUMBER() OVER(PARTITION BY PHONENUM ORDER BY LOGINTIME) AS NUM,RANK() OVER (ORDER BY PHONENUM) AS NUM2FROM TB),MU2 AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY PHONENUM ORDER BY LOGINTIME) AS NUM3 FROM MU T1WHERE NOT EXISTS(SELECT 1 FROM MU T2 WHERE T2.NUM2=T1.NUM2 AND T2.NUM=T1.NUM-1 AND T2.STATUS='熄火') AND STATUS='熄火'),MU3 AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY PHONENUM ORDER BY LOGINTIME) AS NUM3 FROM MU T1WHERE NOT EXISTS(SELECT 1 FROM MU T2 WHERE T2.NUM2=T1.NUM2 AND T2.NUM=T1.NUM+1 AND T2.STATUS='熄火') AND STATUS='熄火')SELECT MU2.PHONENUM,MU2.LOGINTIME,MU3.LOGINTIMEFROM MU2INNER JOIN MU3 ON MU2.PHONENUM=MU3.PHONENUM AND MU2.NUM3=MU3.NUM3/*15308615031    2011-09-04 00:01:51.000    2011-09-04 00:01:52.00015308615031    2011-09-04 00:01:55.000    2011-09-04 00:01:56.00015308615032    2011-09-04 00:01:54.000    2011-09-04 00:01:55.000*/
[解决办法]
http://topic.csdn.net/u/20090917/16/dc77bfd0-78e9-4837-9b46-388446691676.html?53633

与这个的差别就在于没有合并列值

热点排行

SQL Server

分段查询记录