如何在MySQL中查询存在7个连续非零值的记录？

针对15列（首列为id，其余14列为数值型）的表，无需堆砌大量OR操作，推荐以下两种简洁实现方式：

方法1：列转行+窗口函数统计连续非零段（MySQL 8.0+适用） #

通过将列转为行并标记分组，统计连续非零列的长度：

SELECT id
FROM (
    SELECT 
        id,
        col_value,
        -- 用SUM窗口函数给非零段分组：遇到0则分组ID递增，非零则延续上一组
        SUM(CASE WHEN col_value = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY col_idx) AS grp
    FROM (
        -- 将14个数值列逐行展开，col_idx保证列的原始顺序
        SELECT id, 1 AS col_idx, col1 AS col_value FROM your_table
        UNION ALL SELECT id, 2, col2 FROM your_table
        UNION ALL SELECT id, 3, col3 FROM your_table
        UNION ALL SELECT id, 4, col4 FROM your_table
        UNION ALL SELECT id, 5, col5 FROM your_table
        UNION ALL SELECT id, 6, col6 FROM your_table
        UNION ALL SELECT id, 7, col7 FROM your_table
        UNION ALL SELECT id, 8, col8 FROM your_table
        UNION ALL SELECT id, 9, col9 FROM your_table
        UNION ALL SELECT id, 10, col10 FROM your_table
        UNION ALL SELECT id, 11, col11 FROM your_table
        UNION ALL SELECT id, 12, col12 FROM your_table
        UNION ALL SELECT id, 13, col13 FROM your_table
        UNION ALL SELECT id, 14, col14 FROM your_table
    ) AS unpivoted
) AS grouped
WHERE col_value != 0
GROUP BY id, grp
HAVING COUNT(*) >= 7;

逻辑说明：先将每列转为一行并标记顺序，再通过窗口函数划分连续非零的分组，最后统计每个分组的长度，筛选出长度≥7的记录ID。

方法2：用户变量统计连续非零数（MySQL 5.x版本适用） #

用自定义变量累计连续非零的次数，遇到0则重置：

SELECT DISTINCT id
FROM (
    SELECT 
        id,
        col_value,
        @cnt := CASE 
            WHEN col_value != 0 THEN @cnt + 1 
            ELSE 0 
        END AS consecutive_non_zero
    FROM (
        -- 同方法1，展开所有数值列
        SELECT id, 1 AS col_idx, col1 FROM your_table UNION ALL
        SELECT id, 2, col2 FROM your_table UNION ALL
        SELECT id, 3, col3 FROM your_table UNION ALL
        SELECT id, 4, col4 FROM your_table UNION ALL
        SELECT id, 5, col5 FROM your_table UNION ALL
        SELECT id, 6, col6 FROM your_table UNION ALL
        SELECT id, 7, col7 FROM your_table UNION ALL
        SELECT id, 8, col8 FROM your_table UNION ALL
        SELECT id, 9, col9 FROM your_table UNION ALL
        SELECT id, 10, col10 FROM your_table UNION ALL
        SELECT id, 11, col11 FROM your_table UNION ALL
        SELECT id, 12, col12 FROM your_table UNION ALL
        SELECT id, 13, col13 FROM your_table UNION ALL
        SELECT id, 14, col14 FROM your_table
    ) AS unpivoted,
    (SELECT @cnt := 0) AS init
    ORDER BY id, col_idx
) AS counted
WHERE consecutive_non_zero >= 7;

逻辑说明：初始化变量@cnt为0，遍历每行时，非零则累加计数，遇0则重置为0，最后筛选出计数≥7的记录ID。

补充：简化版多列判断（仅适用于固定列数场景） #

如果不想列转行，也可以通过构造连续7列的非零判断，相比全量OR更规整：

SELECT id
FROM your_table
WHERE 
    -- 检查col1-col7是否全非零
    (col1 !=0 AND col2 !=0 AND col3 !=0 AND col4 !=0 AND col5 !=0 AND col6 !=0 AND col7 !=0)
    OR
    -- 检查col2-col8是否全非零
    (col2 !=0 AND col3 !=0 AND col4 !=0 AND col5 !=0 AND col6 !=0 AND col7 !=0 AND col8 !=0)
    OR
    (col3 !=0 AND col4 !=0 AND col5 !=0 AND col6 !=0 AND col7 !=0 AND col8 !=0 AND col9 !=0)
    OR
    (col4 !=0 AND col5 !=0 AND col6 !=0 AND col7 !=0 AND col8 !=0 AND col9 !=0 AND col10 !=0)
    OR
    (col5 !=0 AND col6 !=0 AND col7 !=0 AND col8 !=0 AND col9 !=0 AND col10 !=0 AND col11 !=0)
    OR
    (col6 !=0 AND col7 !=0 AND col8 !=0 AND col9 !=0 AND col10 !=0 AND col11 !=0 AND col12 !=0)
    OR
    (col7 !=0 AND col8 !=0 AND col9 !=0 AND col10 !=0 AND col11 !=0 AND col12 !=0 AND col13 !=0)
    OR
    (col8 !=0 AND col9 !=0 AND col10 !=0 AND col11 !=0 AND col12 !=0 AND col13 !=0 AND col14 !=0);

此方法需手动写出所有连续7列的组合（共8组），适合列数固定且较少的场景。

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