MySQL查询优化：新增nb_beverage列且不改变SUM(1)统计结果

问题分析 #

原查询通过EXISTS关联m_schedule，按id_committee分组统计委员会的文件夹总数（total）及各类意见对应的文件夹数量。现在需要新增nb_beverage列，统计对应委员会下type_schedule=1的记录数，但直接关联m_schedule会导致主表记录被重复匹配，使原有统计结果（total、各opinion计数）出现偏差。

解决方案思路 #

核心是将m_schedule的统计逻辑独立成子查询，避免与主查询的表关联产生笛卡尔积：

1. 单独对m_schedule按id_committee分组，统计每个委员会的type_schedule=1记录数，得到临时统计结果。
2. 将原查询的结果与这个临时统计结果做左连接，既不破坏原有文件夹统计逻辑，又能获取所需的nb_beverage数据。

示例代码 #

假设原查询简化结构如下：

SELECT
    c.id_committee,
    COUNT(f.id_folder) AS total,
    SUM(CASE WHEN f.opinion = 'approve' THEN 1 ELSE 0 END) AS nb_approve,
    SUM(CASE WHEN f.opinion = 'reject' THEN 1 ELSE 0 END) AS nb_reject
FROM m_committee c
JOIN m_foldercommittee fc ON c.id_committee = fc.id_committee
JOIN m_folder f ON fc.id_folder = f.id_folder
WHERE EXISTS (
    SELECT 1 FROM m_schedule s 
    WHERE s.id_committee = c.id_committee 
    -- 原EXISTS的其他过滤条件
)
GROUP BY c.id_committee;

修改后的查询：

SELECT
    main.id_committee,
    main.total,
    main.nb_approve,
    main.nb_reject,
    COALESCE(s_agg.nb_beverage, 0) AS nb_beverage
FROM (
    -- 完全保留原查询的统计逻辑
    SELECT
        c.id_committee,
        COUNT(f.id_folder) AS total,
        SUM(CASE WHEN f.opinion = 'approve' THEN 1 ELSE 0 END) AS nb_approve,
        SUM(CASE WHEN f.opinion = 'reject' THEN 1 ELSE 0 END) AS nb_reject
    FROM m_committee c
    JOIN m_foldercommittee fc ON c.id_committee = fc.id_committee
    JOIN m_folder f ON fc.id_folder = f.id_folder
    WHERE EXISTS (
        SELECT 1 FROM m_schedule s 
        WHERE s.id_committee = c.id_committee 
        -- 原EXISTS的其他过滤条件
    )
    GROUP BY c.id_committee
) main
-- 左连接预先统计好的schedule数据
LEFT JOIN (
    SELECT
        id_committee,
        COUNT(*) AS nb_beverage
    FROM m_schedule
    WHERE type_schedule = 1
    GROUP BY id_committee
) s_agg ON main.id_committee = s_agg.id_committee;

关键说明 #

• 子查询s_agg单独处理m_schedule的统计，确保每个委员会只返回一条结果，避免主查询记录被重复匹配。
• 使用COALESCE函数处理无type_schedule=1记录的委员会，让nb_beverage显示为0而非NULL。
• 原查询逻辑完全保留在main子查询中，确保原有统计结果不受影响。

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