mysql - 在MySQL中以某个值选择连续的行

选择列低于某个值的连续行
我有一张表，上面有以下数据：

crashID     crash
-----------------------
1           189
2           144
3           8939
4           748
5           988
6           102
7           392
8           482
9           185
10          101

我想选择连续行中的最长条纹，这些行的崩溃值也低于某个树阈值。举个例子，假设是500。
如何在一个MySQL查询中执行此操作？（v8.0.1版）
期望输出如下：

crashID     crash
------------------
6           102
7           392
8           482
9           185
10          101

最佳答案

您可以尝试使用间隙和孤岛方法来解决它，假设每次崩溃lte 500都是一个孤岛，然后找到最大的孤岛：

SET @threshold = 500;
WITH cte1 AS (
    SELECT
        crashID,
        CASE WHEN crash <= @threshold THEN 1 ELSE 0 END AS island,
        ROW_NUMBER() OVER (ORDER BY crashID) rn1,
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN crash <= @threshold THEN 1 ELSE 0 END ORDER BY crashID) rn2
    FROM t
), cte2 AS (
    SELECT MIN(crashID) AS fid, MAX(crashID) AS tid
    FROM cte1
    WHERE island = 1
    GROUP BY rn1 - rn2
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
SELECT *
FROM t
WHERE crashID BETWEEN (SELECT fid FROM cte2) AND (SELECT tid FROM cte2);

DB Fiddle