MySQL中能否对连续行执行模式匹配？

首先得帮你理清原SQL的问题所在，你确实把id和val的逻辑搞混了——原语句里firstid+1 = '21'是在找id等于21的行，但你实际需要的是「id比当前行大1的下一行，它的val等于21」，而且别名firstid也不能直接用在WHERE子句里（WHERE执行顺序早于SELECT，别名还没生成）。

针对你的需求（匹配连续行的指定val序列），我给你两种实用的解决方案，适配不同的场景：

场景1：你的表id是连续自增的（像示例里这样） #

这种情况用自连接最直接，先找到符合起始条件的行，再关联出后续的连续行：

方法1：用CTE定位起始行，再选取所有连续行 #

WITH matching_starts AS (
    -- 先找到val=29，且下两行分别是21、14的起始id
    SELECT id AS start_id
    FROM tbl100
    WHERE val = 29
      AND EXISTS (SELECT 1 FROM tbl100 WHERE id = start_id + 1 AND val = 21)
      AND EXISTS (SELECT 1 FROM tbl100 WHERE id = start_id + 2 AND val = 14)
)
-- 选取所有匹配的连续行
SELECT t.id, t.val
FROM tbl100 t
JOIN matching_starts ms ON t.id BETWEEN ms.start_id AND ms.start_id + 2
ORDER BY t.id;

方法2：多表直接连接（更直观） #

如果你想一次性关联出三行，再整合成你要的格式，可以用UNION ALL：

-- 先匹配三行连续的条件，再分别取出每一行
SELECT t1.id, t1.val FROM tbl100 t1
JOIN tbl100 t2 ON t2.id = t1.id + 1
JOIN tbl100 t3 ON t3.id = t2.id + 1
WHERE t1.val = 29 AND t2.val = 21 AND t3.val = 14
UNION ALL
SELECT t2.id, t2.val FROM tbl100 t1
JOIN tbl100 t2 ON t2.id = t1.id + 1
JOIN tbl100 t3 ON t3.id = t2.id + 1
WHERE t1.val = 29 AND t2.val = 21 AND t3.val = 14
UNION ALL
SELECT t3.id, t3.val FROM tbl100 t1
JOIN tbl100 t2 ON t2.id = t1.id + 1
JOIN tbl100 t3 ON t3.id = t2.id + 1
WHERE t1.val = 29 AND t2.val = 21 AND t3.val = 14
ORDER BY id;

这两种方法都能返回你想要的结果：

id | val
----------
3 | 29
4 | 21
5 | 14

场景2：如果id可能不连续（比如中间有缺失） #

如果你的表id不是连续的（比如删除过行），就需要用窗口函数LEAD来获取后续行的val，判断是否匹配模式：

WITH numbered_rows AS (
    SELECT 
        id, 
        val,
        -- 获取下一行的val
        LEAD(val, 1) OVER (ORDER BY id) AS next_val,
        -- 获取下两行的val
        LEAD(val, 2) OVER (ORDER BY id) AS next_next_val
    FROM tbl100
)
-- 先找到起始行，再关联出后续两行
SELECT id, val FROM numbered_rows WHERE val = 29 AND next_val = 21 AND next_next_val = 14
UNION ALL
SELECT (SELECT id FROM tbl100 WHERE val = next_val AND id > numbered_rows.id ORDER BY id LIMIT 1), next_val FROM numbered_rows WHERE val = 29 AND next_val = 21 AND next_next_val = 14
UNION ALL
SELECT (SELECT id FROM tbl100 WHERE val = next_next_val AND id > numbered_rows.id ORDER BY id LIMIT 1), next_next_val FROM numbered_rows WHERE val = 29 AND next_val = 21 AND next_next_val = 14
ORDER BY id;

（注：如果有重复val，用id > numbered_rows.id来确保是当前行之后的行，避免匹配到之前的重复值）

最后再提醒你几个原SQL的关键错误：

• 不要混淆id和val的逻辑：你要匹配的是后续行的val值，不是id值；
• 数值类型的val不需要用单引号包裹（比如val=29而非val='29'，除非val是字符串类型）；
• SELECT的别名不能在WHERE子句中使用，因为WHERE的执行顺序早于SELECT。

内容的提问来源于stack exchange，提问作者blogo