如何修改SQL查询以获取Table1所有FiscalDate对应的数据

要让查询覆盖Table1中所有FiscalDate对应的年月，核心是移除硬编码的年月条件，改为按年月预计算聚合值，再关联到Table2的对应年月。以下是修改后的查询：

WITH MonthlyAggregates AS (
    -- 计算Table1中每个年月的Ledger='100'的Amount总和
    SELECT
        YEAR(FiscalDate) AS Year,
        MONTH(FiscalDate) AS Month,
        SUM(Amount) AS TotalAmount
    FROM dbo.Table1
    WHERE Ledger = '100'
    GROUP BY YEAR(FiscalDate), MONTH(FiscalDate)
),
Table2MonthlyTotals AS (
    -- 计算Table2中每个年月的CommonUnit(KG)总和
    SELECT
        YEAR(RevenueDate) AS Year,
        MONTH(RevenueDate) AS Month,
        SUM([CommonUnit(KG)]) AS TotalCommonUnit
    FROM dbo.Table2
    GROUP BY YEAR(RevenueDate), MONTH(RevenueDate)
)
SELECT
    t2.RevenueDate,
    t2.DeliveredQuantity,
    t2.[CommonUnit(KG)],
    t2.DeliveryUnit,
    CASE
        -- 避免除以0或CommonUnit为0的情况
        WHEN t2.[CommonUnit(KG)] <> 0 AND tm.TotalCommonUnit <> 0
        THEN t2.[CommonUnit(KG)] * (ma.TotalAmount / tm.TotalCommonUnit)
        ELSE NULL
    END AS newcolumn
FROM dbo.Table2 t2
-- 关联Table1的年月聚合数据
JOIN MonthlyAggregates ma
    ON YEAR(t2.RevenueDate) = ma.Year
    AND MONTH(t2.RevenueDate) = ma.Month
-- 关联Table2的年月聚合数据
JOIN Table2MonthlyTotals tm
    ON YEAR(t2.RevenueDate) = tm.Year
    AND MONTH(t2.RevenueDate) = tm.Month;

修改说明 #

1. 移除硬编码条件：原查询中子查询的固定年月筛选被替换为按年月分组的聚合查询，自动覆盖Table1中所有存在的年月。
2. 预计算聚合值：用两个CTE分别计算每个年月的总Amount和总CommonUnit，避免重复计算，提升查询效率。
3. 动态关联年月：通过YEAR()和MONTH()函数将Table2的每条记录与对应年月的聚合结果关联，实现动态匹配所有有效年月。
4. 异常处理：增加了对TotalCommonUnit不为0的判断，避免出现除以0的运行错误。

验证示例数据 #

针对你提供的示例数据：

• 2023年4月：Table1总Amount=1500，Table2总CommonUnit=25，计算系数为1500/25=60，每条记录的newcolumn=CommonUnit(KG)*60，与示例输出一致。
• 2023年5月：Table1总Amount=1400，Table2总CommonUnit=50，计算系数为1400/50=28，对应记录的newcolumn=50*28=1400，符合预期。

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