表中 车牌号 经度 纬度 时间(表是按时间排序的)
我要找出经度和纬度相同的时间差 注意 经度和纬度必须是连续相同的记录
比如2,3条记录的经度和纬度相同 5,6条记录的经度和纬度也和2,3条相同
但是第4条记录不同 查询的结果应该是2,3条的时间差 和5,6条的时间差
我要找出经度和纬度相同的时间差 注意 经度和纬度必须是连续相同的记录
比如2,3条记录的经度和纬度相同 5,6条记录的经度和纬度也和2,3条相同
但是第4条记录不同 查询的结果应该是2,3条的时间差 和5,6条的时间差
INSERT t
SELECT '001', 10, 15, '2011-04-06 10:50:50' UNION ALL
SELECT '001', 10, 15, '2011-04-06 10:50:55' UNION ALL
SELECT '001', 12, 18, '2011-04-06 11:00:00' UNION ALL
SELECT '001', 10, 15, '2011-04-06 12:20:00' UNION ALL
SELECT '001', 10, 15, '2011-04-06 12:30:00';
WITH t1
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY 车牌号 ) AS 序号 ,
车牌号 ,
经度 ,
纬度 ,
时间
FROM dbo.t
)
SELECT a.车牌号 ,
a.经度 ,
a.纬度 ,
a.时间 AS 开始时间 ,
b.时间 AS 结束时间 ,
RIGHT(CONVERT(CHAR(19), DATEADD(s, DATEDIFF(s, a.时间, b.时间),
'00:00:00'), 113),8) AS 停车时间
FROM t1 a
JOIN t1 b ON a.序号 = b.序号 - 1
WHERE a.经度 = b.经度
AND a.纬度 = b.纬度
/*
车牌号 经度 纬度 开始时间 结束时间 停车时间
---------- ----------- ----------- ----------------------- ----------------------- ----------------
001 10 15 2011-04-06 10:50:50.000 2011-04-06 10:50:55.000 00:00:05
001 10 15 2011-04-06 12:20:00.000 2011-04-06 12:30:00.000 00:10:00(2 行受影响)
*/2005
CREATE TABLE t(车牌号 VARCHAR(10),经度 INT,纬度 INT,时间 DATETIME)
INSERT t
SELECT '001', 10, 15, '2011-04-06 10:50:50' UNION ALL
SELECT '001', 10, 15, '2011-04-06 10:50:55' UNION ALL
select '001', 10, 15, '2011-04-06 10:55:00' union all
SELECT '001', 12, 18, '2011-04-06 11:00:00' UNION ALL
SELECT '001', 10, 15, '2011-04-06 12:20:00' UNION ALL
SELECT '001', 10, 15, '2011-04-06 12:30:00';
WITH t1
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY 车牌号 ) AS 序号 ,
车牌号 ,
经度 ,
纬度 ,
时间
FROM dbo.t
)
SELECT a.车牌号 ,
a.经度 ,
a.纬度 ,
a.时间 AS 开始时间 ,
b.时间 AS 结束时间 ,
RIGHT(CONVERT(CHAR(19), DATEADD(s, DATEDIFF(s, a.时间, b.时间),
'00:00:00'), 113),8) AS 停车时间
FROM t1 a
JOIN t1 b ON a.序号 = b.序号 - 1
WHERE a.经度 = b.经度
AND a.纬度 = b.纬度
查出来的结果就是这样的
001 10 15 2011-04-06 10:50:50.000 2011-04-06 10:50:55.000 00:00:05
001 10 15 2011-04-06 10:50:55.000 2011-04-06 10:55:00.000 00:04:05
001 10 15 2011-04-06 12:20:00.000 2011-04-06 12:30:00.000 00:10:00但是前3条记录是连续的,我要想一个结果的