sql 查寻某列连续的几个值是否相同

sql 查找某列连续的几个值是否相同我有一个table类似下面nameitemvaluerectimeA0010.982013/7/24 12:12:30

sql 查找某列连续的几个值是否相同
我有一个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相同
[解决办法]

引用:

Quote: 引用:

是求出有三个连续的value相同的name吗？

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个值呢

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）*/;