IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
phonenum VARCHAR(50)
,logintime DATETIME
,status VARCHAR(10)
)
GO
INSERT INTO TB
SELECT '15308615031','2011-09-04 00:01:51','熄火' UNION ALL
SELECT '15308615032','2011-09-04 00:01:54','熄火' UNION ALL
SELECT '15308615031','2011-09-04 00:01:53','停车' UNION ALL
SELECT '15308615031','2011-09-04 00:01:54','停车' UNION ALL
SELECT '15308615031','2011-09-04 00:01:55','熄火' UNION ALL
SELECT '15308615031','2011-09-04 00:01:56','熄火' UNION ALL
SELECT '15308615032','2011-09-04 00:01:52','停车' UNION ALL
SELECT '15308615032','2011-09-04 00:01:53','停车' UNION ALL
SELECT '15308615031','2011-09-04 00:01:52','熄火' UNION ALL
SELECT '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 NUM2
FROM TB
),MU2 AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY PHONENUM ORDER BY LOGINTIME) AS NUM3 
FROM MU T1
WHERE 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 T1
WHERE 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.LOGINTIME
FROM MU2
INNER 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.000
15308615031    2011-09-04 00:01:55.000    2011-09-04 00:01:56.000
15308615032    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

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