我有一个table类似下面name item value rectime
A 001 0.98 2013/7/24 12:12:30
A 002 0.98 2013/7/23 09:06:50
A 002 0.95 2013/7/23 08:00:00
B 004 0.95 2013/7/24 10:10:30
B 002 0.95 2013/7/24 03:05:00
C 005 0.95 2013/7/24 12:12:30我想实现 group by name ,order by rectime desc找出是否有 3个连续的value相同
A 001 0.98 2013/7/24 12:12:30
A 002 0.98 2013/7/23 09:06:50
A 002 0.95 2013/7/23 08:00:00
B 004 0.95 2013/7/24 10:10:30
B 002 0.95 2013/7/24 03:05:00
C 005 0.95 2013/7/24 12:12:30我想实现 group by name ,order by rectime desc找出是否有 3个连续的value相同
解决方案 »
- 想要用procedure修改某幾筆資料,懇請大大指導..
- 历史表
- !!!!哪位高手能帮忙解下这两道Oracle题,
- 请教达人 一个大数据量的删除的时间问题
- 我错误删除了数据库,用truncate进行删除的,请问还能否恢复?急
- 一条SQL语句....
- SQL结果字串加减处理的问题,大家快来帮忙呀!^_^
- 请帮我看看,我的函数为什么出错?
- 为什么asp对oracle进行图片上传,会出问题?字段类型为:blob型?大虾求命啊!
- pl SQL登陆问题 新手求大神指点!!!!
- oracle 外键约束 导致未明确定义列
- com.inspur.trans.exceptions.DaoException: ORA-00933: SQL 命令未正确结束
with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual)
select distinct t1.name
from (select t.*,lag(value)over(partition by name order by rectime desc)lag_value,
lead(value)over(partition by name order by rectime desc)lead_value from t )t1
where t1.value=nvl(lag_value,0) and t1.value=nvl(lead_value,0);
恩,满足条件的话,就返回name 值
错位的方法我也想过我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢
(select name, value, min(rectime) min_time, max(rectime) max_time from table
group by name ,value having count(*)>=3)v
where t.name=v.name and t.value=v.value
and not exists(select 1 from table tt where tt.name=v.name and tt.value<>v.value
and tt.rectime>v.min_time and tt.rectime<v.max_time)
恩,满足条件的话,就返回name 值
错位的方法我也想过我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢
with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.98 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual),
t1 as (select name,item,value,decode(value,lag(value)over(partition by name order by rectime desc),1,0)is_value,rectime from t),
t2 as (select name,sum(t1.is_value)over(partition by name order by rectime desc
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)sum_is_value from t1) /*查询4个连续的value相同(5个把4 PRECEDING替换成5 PRECEDING)*/
select distinct name
from t2
where t2.sum_is_value>=3 /*查询4个连续的value相同(5个把sum_is_value>=3替换成sum_is_value>=4)*/;
恩,满足条件的话,就返回name 值
错位的方法我也想过我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢
with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.98 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual),
t1 as (select name,item,value,decode(value,lag(value)over(partition by name order by rectime desc),1,0)is_value,rectime from t),
t2 as (select name,sum(t1.is_value)over(partition by name order by rectime desc
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)sum_is_value from t1) /*查询4个连续的value相同(5个把4 PRECEDING替换成5 PRECEDING)*/
select distinct name
from t2
where t2.sum_is_value>=3 /*查询4个连续的value相同(5个把sum_is_value>=3替换成sum_is_value>=4)*/;
这个不错