如何将多段SQL统计查询结果合并为单行单表并获取总数？

可以用条件聚合（结合SUM()和CASE表达式）来实现，只需要扫描一次目标表，比执行四次独立查询更高效，同时直接返回包含所有统计项及总数的单行结果：

SELECT
    SUM(CASE WHEN ACTIVE_FLAG = 1 THEN 1 ELSE 0 END) AS OpenTaskCount,
    SUM(CASE WHEN ACTIVE_FLAG = 0 THEN 1 ELSE 0 END) AS AnsweredTaskCount,
    SUM(CASE WHEN ACTIVE_FLAG = 1 AND TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) THEN 1 ELSE 0 END) AS CandidateTaskCount,
    SUM(CASE WHEN ACTIVE_FLAG = 1 AND TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) THEN 1 ELSE 0 END) AS AlertTaskCount,
    COUNT(*) AS TotalActiveTaskCount
FROM [COM].[USER_GENERATED_TASK_LIST]
WHERE TASK_STATUS = 'Active';

关键说明： #

• 外层WHERE统一过滤TASK_STATUS = 'Active'的记录，减少数据扫描范围
• 每个CASE表达式对应原独立查询的过滤逻辑，符合条件返回1、否则返回0，通过SUM()累加得到对应统计值
• 总数TotalActiveTaskCount直接用COUNT(*)计算，和四个统计项的总和结果一致，无需额外求和运算

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