有如下记录
phonenum logintime status
15308615031 2011-09-04 00:01:51 熄火
15308615031 2011-09-04 00:01:52 熄火
15308615031 2011-09-04 00:01:53 停车
15308615031 2011-09-04 00:01:54 停车
15308615031 2011-09-04 00:01:55 熄火
15308615031 2011-09-04 00:01:56 熄火
15308615032 2011-09-04 00:01:52 停车
15308615032 2011-09-04 00:01:53 停车
15308615032 2011-09-04 00:01:54 熄火
15308615032 2011-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请高手指点啊
phonenum logintime status
15308615031 2011-09-04 00:01:51 熄火
15308615031 2011-09-04 00:01:52 熄火
15308615031 2011-09-04 00:01:53 停车
15308615031 2011-09-04 00:01:54 停车
15308615031 2011-09-04 00:01:55 熄火
15308615031 2011-09-04 00:01:56 熄火
15308615032 2011-09-04 00:01:52 停车
15308615032 2011-09-04 00:01:53 停车
15308615032 2011-09-04 00:01:54 熄火
15308615032 2011-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请高手指点啊
解决方案 »
- datetime类型字段如何用0占位?
- 求助,把SELECT语句改成UPDATE
- 游标内如何控制记录移动?
- 如何建立这样的视图
- 如何将C# 的DATASET 直接写入(Insert)Oracle?
- 错误提示:提示”[Microsoft][ODBC SQL Server Driver]COUNT 字段不正确或语法错误“
- 如何查询表里面不重复的记录
- 请教一个ACESS数据库的问题
- 在SQL,中@ch=('1','2') ,select * from table where code in @ch ,这时要将@ch的值 带入SQL语句中,问应该怎么办 ??
- 小问题,但我不会,各位兄弟指教!
- windows7下sql2000无法新建数据库表
- 这是什么加密
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
*/