mysql - 计算mysql中的连续行

Question

我想在这里找到连续 1 出现 3 次或更长时间的 id。所以在输出中我只会看到 5,6,7,8。有人可以在这里帮我查询吗？

+------+----------+
| id   | diff_chk |
+------+----------+
|    2 |        1 |
|    3 |        2 |
|    5 |        1 |
|    6 |        1 |
|    7 |        1 |
|    8 |        1 |
|    9 |        3 |
|   12 |        1 |
|   13 |        1 |
+------+----------

预期产出；

id
5
6
7
8

我尝试使用的查询没有获取我使用以下逻辑的正确结果。

 select id,COUNT(1) OVER( PARTITION BY diff_chk ORDER BY id ) as w_func from table1 where w_func!=1

标签： mysqlsql

Answer 1

这是差距和孤岛问题。尝试以下操作，这里是MySql 8.0.

select
    id
from
(   
  select
        diff_chck,
        id,
        count(*) over (partition by diff_chck, rnk) as total
    from
    (
        select
            diff_chck,
            id,
            id - row_number() over (partition by diff_chck order by id) as rnk
        from myTable
    ) t
) vals
where total > 2

order by
    id

输出：

*-----*
| id  |
*-----*
| 5   |
| 6   |
| 7   |
| 8   |
*-----*