SQL Server查询结果异常：将两张费用表转为指定分期格式需求

我来帮你把两张宽表的数据转换成你需要的分期付款格式，步骤很清晰，咱们一步步来：

第一步：把宽表转成行结构（UNPIVOT） #

首先得把两张表里的月度列（apr、may...这些）拆成每行对应一个分期的结构，这样才能方便后续计算和关联。

处理应付费用表（Fee_Payable_to_Students） #

用UNPIVOT把列转成行，再按季度分组求和（你提到的Quar...应该是指季度分期吧？如果要月度的话后面我会说怎么改）：

SELECT 
    S_Adm_No,
    -- 把月份映射到对应的季度
    CASE 
        WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
        WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
        WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
        WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
    END AS Installment,
    SUM(amount) AS Total_Payable
FROM Fee_Payable_to_Students
UNPIVOT (
    amount FOR month_col IN (apr, may, june, jul, aug, sep, oct, nov, dec, jan, feb, mar)
) AS up
GROUP BY S_Adm_No, 
    CASE 
        WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
        WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
        WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
        WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
    END

处理减免费用表（Fee_Assign_Waiver_to_Students） #

同样用UNPIVOT转结构，按季度求和：

SELECT 
    S_Adm_No,
    CASE 
        WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
        WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
        WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
        WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
    END AS Installment,
    SUM(amount) AS Total_Waiver
FROM Fee_Assign_Waiver_to_Students
UNPIVOT (
    amount FOR month_col IN (apr, may, june, jul, aug, sep, oct, nov, dec, jan, feb, mar)
) AS up
GROUP BY S_Adm_No, 
    CASE 
        WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
        WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
        WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
        WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
    END

第二步：关联两张表计算最终应付金额 #

把上面两个查询用CTE封装，然后关联计算实际应付金额（应付-减免），同时加上付款日期（这里默认季度最后一天，你可以根据实际需求调整）：

WITH PayableCTE AS (
    SELECT 
        S_Adm_No,
        CASE 
            WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
            WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
            WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
            WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
        END AS Installment,
        SUM(amount) AS Total_Payable,
        -- 定义每个季度的付款日期
        CASE 
            WHEN month_col IN ('apr','may','june') THEN '202X-06-30'
            WHEN month_col IN ('jul','aug','sep') THEN '202X-09-30'
            WHEN month_col IN ('oct','nov','dec') THEN '202X-12-31'
            WHEN month_col IN ('jan','feb','mar') THEN '202X-03-31'
        END AS Payable_Date
    FROM Fee_Payable_to_Students
    UNPIVOT (
        amount FOR month_col IN (apr, may, june, jul, aug, sep, oct, nov, dec, jan, feb, mar)
    ) AS up
    GROUP BY S_Adm_No, 
        CASE 
            WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
            WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
            WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
            WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
        END,
        CASE 
            WHEN month_col IN ('apr','may','june') THEN '202X-06-30'
            WHEN month_col IN ('jul','aug','sep') THEN '202X-09-30'
            WHEN month_col IN ('oct','nov','dec') THEN '202X-12-31'
            WHEN month_col IN ('jan','feb','mar') THEN '202X-03-31'
        END
),
WaiverCTE AS (
    SELECT 
        S_Adm_No,
        CASE 
            WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
            WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
            WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
            WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
        END AS Installment,
        SUM(amount) AS Total_Waiver
    FROM Fee_Assign_Waiver_to_Students
    UNPIVOT (
        amount FOR month_col IN (apr, may, june, jul, aug, sep, oct, nov, dec, jan, feb, mar)
    ) AS up
    GROUP BY S_Adm_No, 
        CASE 
            WHEN month_col IN ('apr','may','june') THEN 'Q2 (Apr-Jun)'
            WHEN month_col IN ('jul','aug','sep') THEN 'Q3 (Jul-Sep)'
            WHEN month_col IN ('oct','nov','dec') THEN 'Q4 (Oct-Dec)'
            WHEN month_col IN ('jan','feb','mar') THEN 'Q1 (Jan-Mar)'
        END
)
SELECT 
    p.S_Adm_No,
    p.Installment,
    -- 用ISNULL防止减免表没有数据的情况
    (p.Total_Payable - ISNULL(w.Total_Waiver, 0)) AS Amount,
    p.Payable_Date
FROM PayableCTE p
LEFT JOIN WaiverCTE w ON p.S_Adm_No = w.S_Adm_No AND p.Installment = w.Installment
-- 按季度顺序排序
ORDER BY 
    CASE p.Installment
        WHEN 'Q1 (Jan-Mar)' THEN 1
        WHEN 'Q2 (Apr-Jun)' THEN 2
        WHEN 'Q3 (Jul-Sep)' THEN 3
        WHEN 'Q4 (Oct-Dec)' THEN 4
    END;

最终结果展示 #

针对你的测试数据，执行后会得到这样的结果：

额外说明 #

如果想要月度分期而不是季度，只需要把所有的CASE语句替换成直接用month_col作为Installment，比如把CASE ... END AS Installment改成month_col AS Installment，然后Payable_Date改成对应月份的最后一天（比如'202X-' + CASE month_col WHEN 'jan' THEN '01-31' ... END）就行啦。

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