SQL 查找某列连续的几个值是否相同

最新推荐文章于 2024-10-10 10:27:31 发布

Vicky-一名菜鸟咨询顾问

最新推荐文章于 2024-10-10 10:27:31 发布

阅读量1.3k

点赞数

分类专栏： SQL 文章标签： SQL

原文链接：https://www.debugease.com/oracle/3383065.html

版权

SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

我有一个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相同

解决方案 »

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

-----------------------------HANA SQL-------------------------------
select distinct t1.name ,t1.value
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 
		(select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dummy
    	 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 dummy
     	 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 dummy
   	     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 dummy
     	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 dummy
     	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 dummy) as t) 
     as t1
where t1.value=coalesce(lag_value,0) and t1.value=coalesce(lead_value,0);

恩，满足条件的话，就返回name 值
错位的方法我也想过我这边相同的3个值只是个例子，如果变成相同的4个，5个值呢

写个容易理解的，先找出三次以上，然后判断期间没有不同valueselect * from table t,
(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)

having>=3的逻辑错了，思路仅供参考。

恩，满足条件的话，就返回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）/;