如何优化SQL查询实现按条件拼接的FinalGroupId字段计算

最优实现方案 #

SQL语法中同层SELECT定义的别名TotalCount无法直接在同层其他字段计算中引用，最优方案是通过CTE（公共表表达式）封装原有统计逻辑，避免重复执行关联统计，性能最优：

WITH base_data AS (
    SELECT 
        id, 
        (SELECT COUNT(1) FROM table2 WHERE table2.Id = table1.Id AND table2.IsActive = 1) AS TotalCount,
        groupid
    FROM table1
)
SELECT 
    id,
    TotalCount,
    groupid AS GroupId,
    CASE 
        WHEN TotalCount > 1 AND groupid IS NOT NULL 
        THEN CONCAT(groupid, '-', TotalCount)
        ELSE groupid 
    END AS FinalGroupId
FROM base_data;

兼容说明 #

• 若使用的数据库不支持CTE（如MySQL 5.7及更低版本），可将base_data子查询直接放在FROM后作为临时表，逻辑和执行效率与CTE完全一致
• 部分数据库支持更简洁的字符串拼接语法，比如Oracle可直接用||运算符替代CONCAT函数，写法为groupid || '-' || TotalCount，效果完全相同
• 不建议直接在CASE中重复写统计子查询，会导致相同的关联计数逻辑执行多次，数据量大时性能下降明显

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