SQL Server 2014实现按月补全TenDays数据行求助

我来帮你搞定这个SQL需求！看起来你需要补全每个年月下缺失的旬（TenDays=1、2、3）数据，并且把没有对应业务记录的旬的合计值填充为0，下面一步步来解决：

原始数据表 #

year month TenDays TotalPerTenDays
------------------------------------------
96 9 3 12
96 10 1 15
96 10 3 22
96 11 1 2
96 11 2 10
96 11 3 1
96 12 1 13

期望的查询结果 #

year month TenDays TotalPerTenDays
------------------------------------------
96 9 1 0
96 9 2 0
96 9 3 12
96 10 1 15
96 10 2 0
96 10 3 22
96 11 1 2
96 11 2 10
96 11 3 1
96 12 1 13
96 12 2 0
96 12 3 0

你当前使用的查询语句 #

SELECT dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays, Sum(dbo.ChequeItemTreasurer.ChequeTreasurer) AS TrTotalMonth
FROM dbo.DateTable LEFT OUTER JOIN dbo.ChequeItemTreasurer ON dbo.DateTable.ShamsiDateLong = dbo.ChequeItemTreasurer.ChequeDateTreasurer
GROUP BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays
ORDER BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays

解决方案 #

你的核心问题有两个：一是要确保维度表包含所有需要的(year, month, TenDays)组合，二是要把聚合后出现的NULL值替换为0。分两种情况处理：

情况1：你的DateTable已经包含所有年月的1、2、3旬 #

如果DateTable本身已经有每个年月对应的3个旬的记录，那只需要修改原查询，用ISNULL()把SUM()的结果中的NULL替换成0就行：

SELECT 
    dbo.DateTable.Year, 
    dbo.DateTable.Month, 
    dbo.DateTable.TenDays, 
    ISNULL(SUM(dbo.ChequeItemTreasurer.ChequeTreasurer), 0) AS TotalPerTenDays
FROM dbo.DateTable 
LEFT OUTER JOIN dbo.ChequeItemTreasurer 
    ON dbo.DateTable.ShamsiDateLong = dbo.ChequeItemTreasurer.ChequeDateTreasurer
GROUP BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays
ORDER BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays;

为什么这样改？因为LEFT JOIN会保留DateTable的所有记录，当某个旬没有匹配的业务数据时，SUM()计算出来的结果是NULL，ISNULL()会把这个NULL转换成你需要的0。

情况2：你的DateTable缺少部分年月的旬记录 #

如果DateTable本身没有覆盖所有年月的1、2、3旬，那需要先生成一个完整的维度表，再关联业务数据：

-- 第一步：生成所有可能的旬（1、2、3）
WITH AllTenDays AS (
    SELECT 1 AS TenDays UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
),
-- 第二步：获取所有存在的年月组合
AllYearMonths AS (
    SELECT DISTINCT Year, Month 
    FROM dbo.DateTable
)
-- 第三步：生成完整的年月旬维度表，然后关联业务表计算合计
SELECT 
    aym.Year, 
    aym.Month, 
    atd.TenDays, 
    ISNULL(SUM(ct.ChequeTreasurer), 0) AS TotalPerTenDays
FROM AllYearMonths aym
CROSS JOIN AllTenDays atd
LEFT JOIN dbo.ChequeItemTreasurer ct 
    -- 这里假设业务表有Year、Month、TenDays字段用来关联
    -- 如果还是要用ShamsiDateLong关联，需要从DateTable获取对应的日期值
    ON aym.Year = ct.Year 
    AND aym.Month = ct.Month 
    AND atd.TenDays = ct.TenDays
GROUP BY aym.Year, aym.Month, atd.TenDays
ORDER BY aym.Year, aym.Month, atd.TenDays;

如果必须用ShamsiDateLong关联，那可以调整为从DateTable中获取每个年月旬对应的日期值：

WITH AllTenDays AS (
    SELECT 1 AS TenDays UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
),
AllYearMonths AS (
    SELECT DISTINCT Year, Month 
    FROM dbo.DateTable
),
FullDateDimension AS (
    SELECT 
        aym.Year, 
        aym.Month, 
        atd.TenDays,
        dt.ShamsiDateLong
    FROM AllYearMonths aym
    CROSS JOIN AllTenDays atd
    LEFT JOIN dbo.DateTable dt 
        ON aym.Year = dt.Year 
        AND aym.Month = dt.Month 
        AND atd.TenDays = dt.TenDays
)
SELECT 
    fdd.Year, 
    fdd.Month, 
    fdd.TenDays, 
    ISNULL(SUM(ct.ChequeTreasurer), 0) AS TotalPerTenDays
FROM FullDateDimension fdd
LEFT JOIN dbo.ChequeItemTreasurer ct 
    ON fdd.ShamsiDateLong = ct.ChequeDateTreasurer
GROUP BY fdd.Year, fdd.Month, fdd.TenDays
ORDER BY fdd.Year, fdd.Month, fdd.TenDays;

这样就能确保所有年月的1、2、3旬都出现在结果里，没有业务数据的旬会填充为0。

内容的提问来源于stack exchange，提问作者R. Salehi