如何优化存在重复统计计算的SQL查询语句？

嘿，我注意到你当前的SQL里重复执行了多次结构相似的子查询，这不仅让代码冗余，还会导致数据库重复计算相同的统计值，拖慢查询效率。咱们来把它优化得更高效、更简洁！

问题分析 #

你的核心逻辑是：针对主表t1的每一行，先判断qbinvoices中是否存在相同qbAccNumber和divisionId的记录；如果存在，就取qbinvoices_view中符合条件的计数，否则取qbes...表的计数。但原来的写法每一步判断都要重新执行一次子查询，完全是重复劳动。

优化方案：预计算统计值再关联 #

我们可以先把需要的所有统计值一次性计算好（按qbAccNumber和divisionId分组），再通过JOIN关联到主表，这样每个统计只计算一次，性能和可读性都会提升。

方案1：使用CTE（适用于支持CTE的数据库，如MySQL 8+、PostgreSQL等）

WITH pre_calculated_stats AS (
    -- 先计算qbinvoices的计数
    SELECT 
        qbAccNumber,
        divisionId,
        COUNT(id) AS invoices_count,
        -- 关联计算qbinvoices_view的符合条件计数
        (SELECT COUNT(id) 
         FROM qbinvoices_view v 
         WHERE v.qbAccNumber = t.qbAccNumber 
           AND v.divisionId = t.divisionId 
           AND v.monthStart > 0) AS view_valid_count,
        -- 关联计算qbes...表的计数（补充你原代码中未写完的逻辑即可）
        (SELECT COUNT(id) 
         FROM qbes... e 
         WHERE e.qbAccNumber = t.qbAccNumber 
           AND e.divisionId = t.divisionId) AS es_count
    FROM qbinvoices t
    GROUP BY qbAccNumber, divisionId
)
SELECT 
    t1.*,
    -- 用CASE WHEN判断取值，直接用预计算好的统计值
    CASE 
        WHEN COALESCE(ps.invoices_count, 0) > 0 THEN ps.view_valid_count
        ELSE ps.es_count
    END AS final_count
FROM your_main_table t1 -- 替换成你的主表名称
LEFT JOIN pre_calculated_stats ps 
    ON ps.qbAccNumber = t1.qbAccNumber 
    AND ps.divisionId = t1.divisionId;

方案2：子查询JOIN（兼容所有支持GROUP BY的数据库）

如果你的数据库不支持CTE，用子查询嵌套的方式同样可以实现：

SELECT 
    t1.*,
    CASE 
        WHEN COALESCE(ps.invoices_count, 0) > 0 THEN ps.view_valid_count
        ELSE ps.es_count
    END AS final_count
FROM your_main_table t1 -- 替换成你的主表名称
LEFT JOIN (
    SELECT 
        qbAccNumber,
        divisionId,
        COUNT(id) AS invoices_count,
        (SELECT COUNT(id) 
         FROM qbinvoices_view v 
         WHERE v.qbAccNumber = t.qbAccNumber 
           AND v.divisionId = t.divisionId 
           AND v.monthStart > 0) AS view_valid_count,
        (SELECT COUNT(id) 
         FROM qbes... e 
         WHERE e.qbAccNumber = t.qbAccNumber 
           AND e.divisionId = t.divisionId) AS es_count
    FROM qbinvoices t
    GROUP BY qbAccNumber, divisionId
) ps ON ps.qbAccNumber = t1.qbAccNumber AND ps.divisionId = t1.divisionId;

额外优化建议

• 给qbAccNumber和divisionId这两个字段创建联合索引，不管是统计还是关联查询，都会大幅提升速度。
• 如果qbes...表有额外的过滤条件，记得补充到对应的子查询中，保证统计逻辑和原代码一致。
• 可以用COALESCE函数处理NULL值，避免统计结果为NULL时出现判断错误。

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