寻求更优雅的MySQL查询优化方案：消除WHERE语句重复问题

需求回顾 #

找出拥有至少10个得票超过100的席位（seggio）的列表（lista），并展示对应的席位信息。

表结构 #

CREATE TABLE `scrutini_l` (
    `lista` char(20) NOT NULL,
    `seggio` int(11) NOT NULL,
    `territorio` int(11) NOT NULL,
    `voti` int(11) NOT NULL,
    PRIMARY KEY (`lista`, `seggio`, `territorio`),
    KEY `scrutini_l_ibfk_2` (`seggio`),
    KEY `scrutini_l_ibfk_3` (`territorio`),
    CONSTRAINT `scrutini_l_ibfk_1` FOREIGN KEY (`lista`) REFERENCES `liste` (`nome`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `scrutini_l_ibfk_2` FOREIGN KEY (`seggio`) REFERENCES `seggi` (`numero`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `scrutini_l_ibfk_3` FOREIGN KEY (`territorio`) REFERENCES `seggi` (`territorio`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

现有问题 #

你当前的查询重复使用了WHERE voti > 100条件，显得冗余：

select lista, seggio, territorio
from scrutini_l
where voti > 100
and lista in
    (select lista
     from scrutini_l
     where voti > 100
     group by lista
     having count (*) >= 10);

优雅解决方案 #

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

窗口函数可以一次性完成过滤和分组统计，全程只需写一次voti > 100，逻辑连贯且可读性强：

SELECT lista, seggio, territorio
FROM (
    SELECT 
        lista, seggio, territorio,
        -- 按lista分组，统计当前组内符合过滤条件的记录数
        COUNT(*) OVER (PARTITION BY lista) AS valid_seat_count
    FROM scrutini_l
    WHERE voti > 100
) AS filtered_results
-- 筛选出符合席位数量要求的列表
WHERE valid_seat_count >= 10;

方案2：使用CTE（公共表表达式，MySQL 8.0+） #

通过CTE复用过滤后的数据集，避免重复写过滤条件，代码结构更清晰：

-- 先定义所有得票超过100的席位记录
WITH valid_seats AS (
    SELECT lista, seggio, territorio
    FROM scrutini_l
    WHERE voti > 100
)
-- 从过滤后的记录中，筛选出符合席位数量要求的列表
SELECT vs.lista, vs.seggio, vs.territorio
FROM valid_seats vs
JOIN (
    SELECT lista
    FROM valid_seats
    GROUP BY lista
    HAVING COUNT(*) >= 10
) AS qualified_lists ON vs.lista = qualified_lists.lista;

方案3：兼容低版本MySQL（<8.0） #

如果你的MySQL版本不支持窗口函数和CTE，可以用JOIN替代IN子查询，虽然仍需写两次过滤条件，但执行计划通常比IN子查询更高效，结构也更清晰：

SELECT s.lista, s.seggio, s.territorio
FROM scrutini_l s
-- 关联符合条件的列表数据集
JOIN (
    SELECT lista
    FROM scrutini_l
    WHERE voti > 100
    GROUP BY lista
    HAVING COUNT(*) >= 10
) AS qualified ON s.lista = qualified.lista
WHERE s.voti > 100;

方案对比 #

• 窗口函数/CTE方案：代码最简洁，无重复条件，逻辑一目了然，适合MySQL 8.0及以上版本。
• JOIN方案：兼容低版本MySQL，性能优于原IN子查询方案，结构比原查询更清晰。

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