请问
有表数据如下:S1 S2 S3
A 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 07:00:00 s
A 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y希望select得到结果为
A 2001-01-01 01:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y即S3字段值每次发生变化的第一条记录,要怎么写?
谢谢
有表数据如下:S1 S2 S3
A 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 07:00:00 s
A 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y希望select得到结果为
A 2001-01-01 01:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y即S3字段值每次发生变化的第一条记录,要怎么写?
谢谢
解决方案 »
- 求个toad for oracle 11的有效注册码
- 怎么建立索引消除order by对性能的影响?
- 获取表中未知字段的值(增量数据抽取)
- 关于oracle如何建立数据库的问题
- 有高手在c++中用Odatabase (oracle 公司提供的oledb)访问数据库嘛?
- 视图作表名的问题(我建立视图后查询,和把视图作为动态表名来查询,结果为什么不一样呢)
- 求一个查询语句的写法
- 触发器中如何查询被触发表的其它记录?
- 求一SQL语句:把B表的查询结果插入到A表中,两表结构基本一样,A表比B表多两个字段
- 怎样写语句才能提高blob字段的存取速度?
- 哪位高手帮忙优化一下存储过程
- alter table xx disable table lock问题
select s1,s2,s3,nvl(lag(s3) over(order by rownum),' ') s4 from tb)
where s3 <> s4;
--- -------------------- ---
A 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 07:00:00 s
A 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y已选择10行。已用时间: 00: 00: 00.01
13:51:27 sys@PRACTICE> select s1,s2,s3 from (
13:51:30 2 select s1,s2,s3,nvl(lag(s3) over(order by rownum),' ') s4 from tb)
13:51:30 3 where s3 <> s4;S1 S2 S3
--- -------------------- ---
A 2001-01-01 01:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y已用时间: 00: 00: 00.01
比如
S1 S2 S3
A 2001-01-01 01:00:00 y
B 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
B 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
B 2001-01-01 03:00:00 n
A 2001-01-01 04:00:00 n
B 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
B 2001-01-01 05:00:00 y
A 2001-01-01 06:00:00 s
B 2001-01-01 06:00:00 y
A 2001-01-01 07:00:00 s
B 2001-01-01 07:00:00 y
A 2001-01-01 08:00:00 y
B 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
B 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y
B 2001-01-01 10:00:00 y希望select得到结果为
A 2001-01-01 01:00:00 y
B 2001-01-01 01:00:00 y
B 2001-01-01 03:00:00 n
A 2001-01-01 04:00:00 n
B 2001-01-01 06:00:00 y
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y 要怎么写呢??
那么这个表里记录了每小时检测一次的服务器状态,y是正常,n是异常,s是故障那么如何得到每台机器每次状态发生变化的时间汇总
早这样描述就好了。。
select s1,s2,s3 from (
select s1,s2,s3,nvl(lag(s3) over(partition by s1 order by rownum),' ') s4 from tb)
where s3 <> s4;
--- -------------------- ---
A 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 07:00:00 s
A 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y
B 2001-01-01 01:00:00 y
B 2001-01-01 02:00:00 y
B 2001-01-01 03:00:00 y
B 2001-01-01 04:00:00 n
B 2001-01-01 05:00:00 n
B 2001-01-01 06:00:00 s
B 2001-01-01 07:00:00 s
B 2001-01-01 08:00:00 y
B 2001-01-01 09:00:00 y
B 2001-01-01 10:00:00 y已选择20行。已用时间: 00: 00: 00.03
15:07:51 sys@PRACTICE> select s1,s2,s3 from (
15:07:56 2 select s1,s2,s3,nvl(lag(s3) over(partition by s1 order by rownum),' ') s4 from tb)
15:07:56 3 where s3 <> s4;S1 S2 S3
--- -------------------- ---
A 2001-01-01 01:00:00 y
A 2001-01-01 04:00:00 n
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y
B 2001-01-01 01:00:00 y
B 2001-01-01 04:00:00 n
B 2001-01-01 06:00:00 s
B 2001-01-01 08:00:00 y已选择8行。已用时间: 00: 00: 00.01
SELECT s1, s2, s3
FROM (SELECT t.*,
lag(s1, 1, 'not exists') over(ORDER BY t.s2) s4,
lag(s3, 1, 'not exists') over(ORDER BY t.s2) s5
FROM t5 t)
WHERE s1 <> s4 or
s3 <> s5;
- -------------------- -
A 2001-01-01 01:00:00 y
B 2001-01-01 01:00:00 y
A 2001-01-01 02:00:00 y
B 2001-01-01 02:00:00 y
A 2001-01-01 03:00:00 y
B 2001-01-01 03:00:00 n
A 2001-01-01 04:00:00 n
B 2001-01-01 04:00:00 n
A 2001-01-01 05:00:00 n
B 2001-01-01 05:00:00 y
A 2001-01-01 06:00:00 s
B 2001-01-01 06:00:00 y
A 2001-01-01 07:00:00 s
B 2001-01-01 07:00:00 y
A 2001-01-01 08:00:00 y
B 2001-01-01 08:00:00 y
A 2001-01-01 09:00:00 y
B 2001-01-01 09:00:00 y
A 2001-01-01 10:00:00 y
B 2001-01-01 10:00:00 y20 rows selected.SQL> select *
2 from a t
3 where not exists (select 1 from a where s1=t.s1 and s2<t.s2)
4 or s3 != (select s3 from a where s1=t.s1 and s2=(select max(s2) from a where s1=t.s1 and s2<t.s2));S S2 S
- -------------------- -
A 2001-01-01 01:00:00 y
B 2001-01-01 01:00:00 y
B 2001-01-01 03:00:00 n
A 2001-01-01 04:00:00 n
B 2001-01-01 05:00:00 y
A 2001-01-01 06:00:00 s
A 2001-01-01 08:00:00 y7 rows selected.SQL>