如何将多表分组统计的每日计数合并为单个SQL结果集

当然可以实现！你可以通过UNION ALL将三个表的每日统计结果先合并，再在外层查询中按日期分组求和，就能得到包含每日总计数的单个数据集了。

具体SQL语句 #

SELECT 
    CompletedDate,
    SUM(Count) AS TotalCount
FROM (
    -- 从Table1获取每日统计
    SELECT CONVERT(Date, CompletedDate) AS CompletedDate, COUNT(ID) AS Count 
    FROM Table1 
    WHERE CompletedDate >= DATEADD(day, -5, GETDATE()) 
    GROUP BY CONVERT(Date, CompletedDate)
    
    UNION ALL
    
    -- 从Table2获取每日统计
    SELECT CONVERT(Date, CompletedDate) AS CompletedDate, COUNT(ID) AS Count 
    FROM Table2 
    WHERE CompletedDate >= DATEADD(day, -5, GETDATE()) 
    GROUP BY CONVERT(Date, CompletedDate)
    
    UNION ALL
    
    -- 从Table3获取每日统计
    SELECT CONVERT(Date, CompletedDate) AS CompletedDate, COUNT(ID) AS Count 
    FROM Table3 
    WHERE CompletedDate >= DATEADD(day, -5, GETDATE()) 
    GROUP BY CONVERT(Date, CompletedDate)
) AS CombinedStats
GROUP BY CompletedDate
ORDER BY CompletedDate;

关键细节说明 #

• 选用UNION ALL而非UNION：UNION会自动对结果去重，但我们需要保留三个表的所有统计行才能准确求和；同时UNION ALL不需要额外的去重操作，性能表现更优。
• 子查询先完成单表统计：先在每个子查询里做好单表的每日计数，再在外层聚合求和，能减少外层需要处理的数据量，提升查询效率。
• 新增ORDER BY排序：最后按日期排序可以让结果按时间顺序展示，更直观易读。

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