sql 查询两个数字连续出现的次数

比如:+----+-----+
| Id | Num |
+----+-----+
| 1 | 4 |
| 2 | 5 |
| 3 | 6 |
| 4 | 9 |
| 5 | 0 |
| 6 | 0 |
| 7 | 3 |
| 8 | 1 |
| 9 | 7 |
|10 | 2 |
+----+-----+
要查询出0跟9连续出现的次数

问题描述不清晰，麻烦说清楚

这个问题如果你不用存储过程的话建议不用sql处理。
将完整的数据传送到后台将num字段存入数组
循环判断一下
很轻易就搞定了

把num在0跟9之间的数据先筛选出来然后根据id是否连续确定次数

https://blog.csdn.net/SoWhatWorld/article/details/104859986

怎样算连续？是id连着？比如说0，id是5和6，算连续？
试试这样

select a.num, count(a.num) as '连续出现次数'
from table a
group by a.num
having count(a.num) = max(a.id)-min(a.id)+1  --次数=连续id的差+1

我来了，来CSDN解决最多的就是这种问题了。

1、创建表

create table test111(
id number, val number
)

2、插入测试数据

insert into TEST111 (ID, VAL)
values ('1', '10');

insert into TEST111 (ID, VAL)
values ('2', '18');

insert into TEST111 (ID, VAL)
values ('3', '10');

insert into TEST111 (ID, VAL)
values ('4', '10');

insert into TEST111 (ID, VAL)
values ('5', '10');

insert into TEST111 (ID, VAL)
values ('6', '10');

insert into TEST111 (ID, VAL)
values ('7', '0');

insert into TEST111 (ID, VAL)
values ('8', '0');

insert into TEST111 (ID, VAL)
values ('9', '0');

insert into TEST111 (ID, VAL)
values ('10', '2');

insert into TEST111 (ID, VAL)
values ('11', '2');

insert into TEST111 (ID, VAL)
values ('12', '9');

insert into TEST111 (ID, VAL)
values ('13', '4');

insert into TEST111 (ID, VAL)
values ('14', '9');

insert into TEST111 (ID, VAL)
values ('15', '9');

insert into TEST111 (ID, VAL)
values ('16', '0');

insert into TEST111 (ID, VAL)
values ('17', '7');
commit;

图片说明

3、输出结果
分别输出0,9,10这三个数字的连续出现的最大次数。
如果不是最大次数，可以去掉最外层的group by。

select val, max(res)
  from (SELECT val, MAX(id) - MIN(id) + 1 res
          FROM (
                --dataset1
                select *
                  from TEST111 j
                 where j.val in (0, 9, 10)
                --end dataset1
                ) t

         START WITH NOT EXISTS (SELECT 1
                       FROM (
                             --dataset1
                             select *
                               from TEST111 j
                              where j.val in (0, 9, 10)
                             --end dataset1
                             ) b
                      WHERE b.id = t.id - 1)
        CONNECT BY PRIOR t.id = t.id - 1
         GROUP BY rownum - LEVEL, val)
 group by val

图片说明

如有更多问题，欢迎联系探讨，QQ/VX:316187205

SELECT (select COUNT(*)
from (select *
from (select a.id,
a.num,
LEAD(a.num, 1) over(order by id asc) as num1
from test a))
where (num = 0 or num = 9)
and (num1 = 0 or num1 = 9)) -
(SELECT COUNT(*)
FROM (SELECT T.ID, LEAD(T.ID, 1) OVER(ORDER BY ID ASC) AS ID2
FROM (select *
from (select *
from (select a.id,
a.num,
LEAD(a.num, 1) over(order by id asc) as num1
from test a))
where (num = 0 or num = 9)
and (num1 = 0 or num1 = 9)) T)
WHERE ID2 - ID = 1)
FROM DUAL

0和9连续出现的概念是啥？是0和9连续就可以还是0 和 9 分别统计，连续次数是什么意思，比如 123 都是 0 算两次连续还是一次连续，我这个是最难算的一种统计，如果连续接连续则计算为一次的那种

select t1.num, count(t2.num) from test t1 left join (
(select id,num
from (select *
from (select a.id,
a.num,
LEAD(a.num, 1) over(order by id asc) as num1
from test a))
where num =num1
minus
select id, num
from (SELECT T.ID, LEAD(T.ID, 1) OVER(ORDER BY ID ASC) AS ID2, num
FROM (select t.*,
row_number() over(partition by NUM order by id asc) rn
from (select id, num
from (select *
from (select a.id,
a.num,
LEAD(a.num, 1) over(order by id asc) as num1
from test a))
where num = num1) t) t)
where id2 - id = 1)) t2 on t1.id =t2.id where (t1.num=0 or t1.num=9)
group by t1.num
group by num 这是计算 0和 9分别连续出现次数的。连续连的超过两个数的，计算为1次