嘿，我来帮你搞定这两个需求！你现在的SQL只能拿到30天的总量，要实现按日统计上月数据并展示，咱们可以用生成日期序列+多表分组统计合并的方式来实现，下面是具体的方案：

核心思路 #

1. 先生成上月的所有日期列表（确保每天都能出现在结果里，哪怕当天没有工作量）
2. 分别对每张表按日期统计当日的记录数
3. 把各表的每日统计结果合并，再按日期汇总总工作量
4. 最终关联日期列表，得到完整的每日数据

具体SQL代码（SQL Server语法） #

-- 生成上月的所有日期列表
WITH LastMonthDates AS (
    SELECT DATEADD(day, number, DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)) AS [Date]
    FROM master.dbo.spt_values
    WHERE type = 'P'
        AND number < DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0), DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
),
-- 统计各表每日的记录数
DailyStats AS (
    -- Incidents表
    SELECT CAST(Created AS DATE) AS [Date], COUNT(Id) AS [Count]
    FROM Incidents
    WHERE Created >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
        AND Created < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY CAST(Created AS DATE)
    
    UNION ALL
    
    -- ApprovedWorks表
    SELECT CAST(WorkStarted AS DATE) AS [Date], COUNT(Id) AS [Count]
    FROM ApprovedWorks
    WHERE WorkStarted >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
        AND WorkStarted < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY CAST(WorkStarted AS DATE)
    
    UNION ALL
    
    -- Demos表
    SELECT CAST([Start] AS DATE) AS [Date], COUNT(Id) AS [Count]
    FROM dbo.Demos
    WHERE [Start] >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
        AND [Start] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY CAST([Start] AS DATE)
    
    UNION ALL
    
    -- AcceptanceTests表
    SELECT CAST([Start] AS DATE) AS [Date], COUNT(Id) AS [Count]
    FROM AcceptanceTests
    WHERE [Start] >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
        AND [Start] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY CAST([Start] AS DATE)
    
    UNION ALL
    
    -- Notifications表
    SELECT CAST([Start] AS DATE) AS [Date], COUNT(Id) AS [Count]
    FROM dbo.Notifications
    WHERE [Start] >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
        AND [Start] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY CAST([Start] AS DATE)
)
-- 合并每日统计，关联日期列表确保无数据日期显示0
SELECT 
    lmd.[Date],
    ISNULL(SUM(ds.[Count]), 0) AS TotalDailyWorkload
FROM LastMonthDates lmd
LEFT JOIN DailyStats ds ON lmd.[Date] = ds.[Date]
GROUP BY lmd.[Date]
ORDER BY lmd.[Date]

代码说明 #

• LastMonthDates CTE：利用系统表master.dbo.spt_values生成上月的所有日期，这样不管哪天有没有数据，结果里都会有对应的日期行。
• DailyStats CTE：对每张表单独按日期（把datetime类型转成date，忽略时间部分）分组统计数量，用UNION ALL合并所有表的统计结果。
• 最终查询：用左连接把日期列表和每日统计关联起来，用ISNULL把无数据的日期的统计值设为0，最后按日期排序，得到清晰的每日数据展示。

这样就能同时满足你的两个需求：既获取了上月每日的对应数据，也统计出了每日的总工作量，结果是按日期顺序展示的。

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