-- 假设你的表名为:tb_namewith a1 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM tb_name WHERE STATUS17='熄火'), a2 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM a1 t1 WHERE EXISTS (SELECT 1 FROM a1 t2 WHERE t2.LONGITUDE=t1.LONGITUDE AND t2.LATITUDE = t1.LATITUDE HAVING MIN(t2.POSITIONTIME) = t1.POSITIONTIME ) ), SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM tb_name WHERE STATUS17='点火' UNION ALL SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM a2 ORDER BY POSITIONTIME;
-- 假设你的表名为:tb_namewith a1 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM tb_name WHERE STATUS17='熄火'), a2 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM a1 t1 WHERE EXISTS (SELECT 1 FROM a1 t2 WHERE t2.LONGITUDE=t1.LONGITUDE AND t2.LATITUDE = t1.LATITUDE HAVING MIN(t2.POSITIONTIME) = t1.POSITIONTIME ) ) SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM tb_name WHERE STATUS17='点火' UNION ALL SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME FROM a2 ORDER BY POSITIONTIME;-- 4楼多了个逗号,将其更正!
SQL> WITH t AS ( 2 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:01' POSITIONTIME FROM DUAL UNION ALL 3 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:02' POSITIONTIME FROM DUAL UNION ALL 4 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:03' POSITIONTIME FROM DUAL UNION ALL 5 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:04' POSITIONTIME FROM DUAL UNION ALL 6 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:05' POSITIONTIME FROM DUAL UNION ALL 7 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:06' POSITIONTIME FROM DUAL UNION ALL 8 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:07' POSITIONTIME FROM DUAL UNION ALL 9 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:08' POSITIONTIME FROM DUAL UNION ALL 10 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:09' POSITIONTIME FROM DUAL UNION ALL 11 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:10' POSITIONTIME FROM DUAL UNION ALL 12 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:11' POSITIONTIME FROM DUAL 13 ) 14 SELECT m.LONGITUDE, 15 m.LATITUDE, 16 m.STATUS19, 17 m.POSITIONTIME 18 FROM (SELECT t.*, 19 LAG(STATUS19, 1) OVER(ORDER BY t.POSITIONTIME) l_status, 20 LAG(POSITIONTIME, 1) OVER(ORDER BY t.POSITIONTIME) l_positiontime 21 FROM t) m 22 WHERE m.STATUS19 <> m.l_status 23 OR STATUS19 = '点火' 24 ORDER BY m.POSITIONTIME 25 ;LONGITUDE LATITUDE STATUS19 POSITIONTIME --------- -------- -------- ------------------- 1234567 11243111 点火 2011-05-16 08:00:01 1234567 11243111 点火 2011-05-16 08:00:02 1234567 11243111 点火 2011-05-16 08:00:03 1234567 11243111 熄火 2011-05-16 08:00:04 1234567 11243111 点火 2011-05-16 08:00:05 1234567 11243111 点火 2011-05-16 08:00:06 1234567 11243111 点火 2011-05-16 08:00:07 1234567 11243111 熄火 2011-05-16 08:00:08 1234567 11243111 点火 2011-05-16 08:00:119 rows selected 你下次提问不要抓图,给出数据的sql
第一条数据是熄火的时候有问题,改下SELECT m.LONGITUDE, m.LATITUDE, m.STATUS19, m.POSITIONTIME FROM (SELECT t.*, LAG(STATUS19, 1, -1) OVER(ORDER BY t.POSITIONTIME) l_status, LAG(POSITIONTIME, 1) OVER(ORDER BY t.POSITIONTIME) l_positiontime FROM t) m WHERE m.STATUS19 <> m.l_status OR STATUS19 = '点火' ORDER BY m.POSITIONTIME
SQL> select * from testxy;VA GD -- ------------------- x 2011-07-12 15:36:01 x 2011-07-12 15:36:02 x 2011-07-12 15:36:03 x 2011-07-12 15:36:04 y 2011-07-12 15:36:08 y 2011-07-12 15:36:11 x 2011-07-12 15:36:17 y 2011-07-12 15:36:22 x 2011-07-12 15:36:26 x 2011-07-12 15:36:27 y 2011-07-12 15:36:31 y 2011-07-12 15:36:33 y 2011-07-12 15:36:33SQL> with y as (select rownum rn ,val,gd from testxy) 2 ,re as ( select y.val,y.gd,(select decode(x.val,'x ','y',null) from y x where x.rn=y.rn-1) b 3 from y 4 where y.val='y') 5 select val,gd from re where b='y';VA GD -- ------------------- y 2011-07-12 15:36:08 y 2011-07-12 15:36:22 y 2011-07-12 15:36:31然后你再并上所有的 x,我觉的你这个问题最大要求其它是y,x反正全部都要,如果数据量大再考虑x 那效率就很低仅供参考
with temp1 as( select * lag(car_state) over( order by car_time)next_state from STATUS17 t ) select * from temp1 where car_state='点火' or next_state='点火'
-- 则将 后面连续的"熄火"记录行去掉(只取第一条) ?
FROM tb_name
WHERE STATUS17='熄火'),
a2 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM a1 t1
WHERE EXISTS (SELECT 1 FROM a1 t2
WHERE t2.LONGITUDE=t1.LONGITUDE
AND t2.LATITUDE = t1.LATITUDE
HAVING MIN(t2.POSITIONTIME) = t1.POSITIONTIME ) ),
SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM tb_name
WHERE STATUS17='点火'
UNION ALL
SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM a2
ORDER BY POSITIONTIME;
FROM tb_name
WHERE STATUS17='熄火'),
a2 as (SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM a1 t1
WHERE EXISTS (SELECT 1 FROM a1 t2
WHERE t2.LONGITUDE=t1.LONGITUDE
AND t2.LATITUDE = t1.LATITUDE
HAVING MIN(t2.POSITIONTIME) = t1.POSITIONTIME ) )
SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM tb_name
WHERE STATUS17='点火'
UNION ALL
SELECT LONGITUDE, LATITUDE, STATUS17, STATUS19, POSITIONTIME
FROM a2
ORDER BY POSITIONTIME;-- 4楼多了个逗号,将其更正!
SQL> WITH t AS (
2 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:01' POSITIONTIME FROM DUAL UNION ALL
3 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:02' POSITIONTIME FROM DUAL UNION ALL
4 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:03' POSITIONTIME FROM DUAL UNION ALL
5 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:04' POSITIONTIME FROM DUAL UNION ALL
6 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:05' POSITIONTIME FROM DUAL UNION ALL
7 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:06' POSITIONTIME FROM DUAL UNION ALL
8 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:07' POSITIONTIME FROM DUAL UNION ALL
9 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:08' POSITIONTIME FROM DUAL UNION ALL
10 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:09' POSITIONTIME FROM DUAL UNION ALL
11 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'熄火' STATUS19,'2011-05-16 08:00:10' POSITIONTIME FROM DUAL UNION ALL
12 SELECT '1234567' LONGITUDE,'11243111' LATITUDE,'点火' STATUS19,'2011-05-16 08:00:11' POSITIONTIME FROM DUAL
13 )
14 SELECT m.LONGITUDE,
15 m.LATITUDE,
16 m.STATUS19,
17 m.POSITIONTIME
18 FROM (SELECT t.*,
19 LAG(STATUS19, 1) OVER(ORDER BY t.POSITIONTIME) l_status,
20 LAG(POSITIONTIME, 1) OVER(ORDER BY t.POSITIONTIME) l_positiontime
21 FROM t) m
22 WHERE m.STATUS19 <> m.l_status
23 OR STATUS19 = '点火'
24 ORDER BY m.POSITIONTIME
25 ;LONGITUDE LATITUDE STATUS19 POSITIONTIME
--------- -------- -------- -------------------
1234567 11243111 点火 2011-05-16 08:00:01
1234567 11243111 点火 2011-05-16 08:00:02
1234567 11243111 点火 2011-05-16 08:00:03
1234567 11243111 熄火 2011-05-16 08:00:04
1234567 11243111 点火 2011-05-16 08:00:05
1234567 11243111 点火 2011-05-16 08:00:06
1234567 11243111 点火 2011-05-16 08:00:07
1234567 11243111 熄火 2011-05-16 08:00:08
1234567 11243111 点火 2011-05-16 08:00:119 rows selected
你下次提问不要抓图,给出数据的sql
m.LATITUDE,
m.STATUS19,
m.POSITIONTIME
FROM (SELECT t.*,
LAG(STATUS19, 1, -1) OVER(ORDER BY t.POSITIONTIME) l_status,
LAG(POSITIONTIME, 1) OVER(ORDER BY t.POSITIONTIME) l_positiontime
FROM t) m
WHERE m.STATUS19 <> m.l_status
OR STATUS19 = '点火'
ORDER BY m.POSITIONTIME
-- -------------------
x 2011-07-12 15:36:01
x 2011-07-12 15:36:02
x 2011-07-12 15:36:03
x 2011-07-12 15:36:04
y 2011-07-12 15:36:08
y 2011-07-12 15:36:11
x 2011-07-12 15:36:17
y 2011-07-12 15:36:22
x 2011-07-12 15:36:26
x 2011-07-12 15:36:27
y 2011-07-12 15:36:31
y 2011-07-12 15:36:33
y 2011-07-12 15:36:33SQL> with y as (select rownum rn ,val,gd from testxy)
2 ,re as ( select y.val,y.gd,(select decode(x.val,'x ','y',null) from y x where x.rn=y.rn-1) b
3 from y
4 where y.val='y')
5 select val,gd from re where b='y';VA GD
-- -------------------
y 2011-07-12 15:36:08
y 2011-07-12 15:36:22
y 2011-07-12 15:36:31然后你再并上所有的 x,我觉的你这个问题最大要求其它是y,x反正全部都要,如果数据量大再考虑x 那效率就很低仅供参考
select * lag(car_state) over( order by car_time)next_state from STATUS17 t
)
select * from temp1
where car_state='点火' or next_state='点火'