数据库表记录有:-----------日期--------------设备状态-------------------
2010-02-05 10:25:10 S
2010-02-05 10:29:55 S
2010-02-05 10:30:12 C
2010-02-05 10:31:10 C
2010-02-05 10:33:15 S
2010-02-05 10:35:20 D
2010-02-05 10:37:19 D
2010-02-05 10:39:55 D 目前查询结果,要求:
1)按时间排序,顺序不能打乱
2)查询[设备状态]不同的记录,如果同一个状态连续多条记录,那么只取这个状态的首条,
比如记录为【A,B,A,A,A,C,C】,那么查询的期望结果应该是【A,B,A,C】上面模拟的数据库记录期望的查询结果应该为:
-----------日期--------------设备状态-------------------
2010-02-05 10:25:10 S
2010-02-05 10:30:12 C
2010-02-05 10:33:15 S
2010-02-05 10:35:20 D 请问Oracle的查询语句应该怎么写才可以实现?
谢谢各位!
2010-02-05 10:25:10 S
2010-02-05 10:29:55 S
2010-02-05 10:30:12 C
2010-02-05 10:31:10 C
2010-02-05 10:33:15 S
2010-02-05 10:35:20 D
2010-02-05 10:37:19 D
2010-02-05 10:39:55 D 目前查询结果,要求:
1)按时间排序,顺序不能打乱
2)查询[设备状态]不同的记录,如果同一个状态连续多条记录,那么只取这个状态的首条,
比如记录为【A,B,A,A,A,C,C】,那么查询的期望结果应该是【A,B,A,C】上面模拟的数据库记录期望的查询结果应该为:
-----------日期--------------设备状态-------------------
2010-02-05 10:25:10 S
2010-02-05 10:30:12 C
2010-02-05 10:33:15 S
2010-02-05 10:35:20 D 请问Oracle的查询语句应该怎么写才可以实现?
谢谢各位!
create table tabs (dt date, status char(1));insert into tabs values(timestamp '2010-02-05 10:25:10','S');
insert into tabs values(timestamp '2010-02-05 10:29:55','S');
insert into tabs values(timestamp '2010-02-05 10:30:12','C');
insert into tabs values(timestamp '2010-02-05 10:31:10','C');
insert into tabs values(timestamp '2010-02-05 10:33:15','S');
insert into tabs values(timestamp '2010-02-05 10:35:20','D');
insert into tabs values(timestamp '2010-02-05 10:37:19','D');
insert into tabs values(timestamp '2010-02-05 10:39:55','D');with t as(
select dt,status,row_number() over (order by status,dt)-row_number() over (order by dt,status) rid from tabs
)
select dt,status from t t1 where not exists (select * from t where rid=t1.rid and dt<t1.dt) order by dt;drop table tabs;/*
DT STATUS
------------------------- ------
2010-02-05 10.25.10 S
2010-02-05 10.30.12 C
2010-02-05 10.33.15 S
2010-02-05 10.35.20 D
*/
select 日期 ,设备状态 from
(
select 日期,设备状态,
lag(设备状态,1) over(order by rownum) 设备状态1,
row_number() over(order by rownum) rn
from tt
)
where 设备状态1 <> 设备状态 or 设备状态1 is null;
如果记录和前一个相同 不显示(select)
下一个!
SQL> with tt as
2 (select to_date('2010-02-05 10:25:10', 'yyyy-mm-dd hh24:mi:ss') 日期,'S' 设备状态 from dual union all
3 select to_date('2010-02-05 10:29:55', 'yyyy-mm-dd hh24:mi:ss') 日期,'S' 设备状态 from dual union all
4 select to_date('2010-02-05 10:30:12', 'yyyy-mm-dd hh24:mi:ss') 日期,'C' 设备状态 from dual union all
5 select to_date('2010-02-05 10:31:10', 'yyyy-mm-dd hh24:mi:ss') 日期,'C' 设备状态 from dual union all
6 select to_date('2010-02-05 10:33:15', 'yyyy-mm-dd hh24:mi:ss') 日期,'S' 设备状态 from dual union all
7 select to_date('2010-02-05 10:35:20', 'yyyy-mm-dd hh24:mi:ss') 日期,'D' 设备状态 from dual union all
8 select to_date('2010-02-05 10:37:19', 'yyyy-mm-dd hh24:mi:ss') 日期,'D' 设备状态 from dual union all
9 select to_date('2010-02-05 10:39:55', 'yyyy-mm-dd hh24:mi:ss') 日期,'D' 设备状态 from dual
10 )
11 select 日期 ,设备状态 from
12 (
13 select 日期,设备状态,
14 lag(设备状态,1) over(order by rownum) 设备状态1,
15 row_number() over(order by rownum) rn
16 from tt
17 )
18 where 设备状态1 <> 设备状态 or 设备状态1 is null;日期
------------------- -
2010-02-05 10:25:10 S
2010-02-05 10:30:12 C
2010-02-05 10:33:15 S
2010-02-05 10:35:20 D
不过问题已解决,谢谢。
group by yourDate,status
order by yourDate
分组 不就好了嘛
忘了重复的问题了,在之前加上一个 distance 就好了
之前自己也想过这个方法