如何编写SQL Server查询按账户统计2020年月度余额

需求说明 #

需要编写SQL Server查询，获取所有账户在2020年1月至12月的月度余额，输出为按账户、年份展示各月余额的行列转换表格。余额计算逻辑：

每月余额 = (Transfer_ins.amount总和 + pix_movements中in_or_out为'pix_in'的pix_amount总和) - (Transfer_outs.amount总和 + pix_movements中in_or_out为'pix_out'的pix_amount总和)

预期输出示例：

Account_id| Year  | Jan |Feb| Mar| Apr|May | Jun| Jul| Aug|Sep| Oct| Nov|Dec
---------  ----  --- --- --- --- --- --- --- --- --- --- --- ---
        1 | 2020  | 200 | 0 |  0 |  0 |  0 |  0 |  0 |  0 | 0 |-500| 400| 0
        2 | 2020  |  0  | 0 |  0 | 500|  0 |  0 | 900|  0 | 0 | 100| 0  | 0
        3 | 2020  | 100 | 0 |  0 |  0 |  0 |  0 | 0  |  0 | 0 | 200| 500| 0

原查询的问题 #

1. 表连接错误：使用INNER JOIN会过滤掉没有转账或Pix交易的账户，应改为LEFT JOIN；同时存在表名拼写错误：pix_movemets应为pix_movements。
2. 语法错误：SELECT列之间缺少逗号分隔；SQL Server中字符串常量需用单引号（原查询用了双引号）；Dec列误写为Dic，且Nov的CASE语句中重复使用了月份11（应为12）。
3. 逻辑错误：多表直接连接会产生笛卡尔积，导致金额重复计算；GROUP BY包含action_month但SELECT中未按月份分组汇总，行列转换逻辑混乱。

正确查询实现 #

方案一：先汇总月度数据，再用CASE语句行列转换 #

WITH MonthlyBalances AS (
    SELECT
        a.account_id,
        YEAR(t.transaction_date) AS balance_year,
        MONTH(t.transaction_date) AS balance_month,
        SUM(CASE WHEN t.transaction_type = 'transfer_in' THEN t.amount ELSE 0 END) +
        SUM(CASE WHEN t.transaction_type = 'pix_in' THEN t.amount ELSE 0 END) AS total_in,
        SUM(CASE WHEN t.transaction_type = 'transfer_out' THEN t.amount ELSE 0 END) +
        SUM(CASE WHEN t.transaction_type = 'pix_out' THEN t.amount ELSE 0 END) AS total_out
    FROM accounts a
    LEFT JOIN (
        -- 合并转入交易
        SELECT account_id, transfer_date AS transaction_date, amount, 'transfer_in' AS transaction_type
        FROM transfer_ins
        WHERE YEAR(transfer_date) = 2020
        UNION ALL
        -- 合并转出交易
        SELECT account_id, transfer_date AS transaction_date, amount, 'transfer_out' AS transaction_type
        FROM transfer_outs
        WHERE YEAR(transfer_date) = 2020
        UNION ALL
        -- 合并Pix转入交易
        SELECT account_id, movement_date AS transaction_date, pix_amount AS amount, 'pix_in' AS transaction_type
        FROM pix_movements
        WHERE in_or_out = 'pix_in' AND YEAR(movement_date) = 2020
        UNION ALL
        -- 合并Pix转出交易
        SELECT account_id, movement_date AS transaction_date, pix_amount AS amount, 'pix_out' AS transaction_type
        FROM pix_movements
        WHERE in_or_out = 'pix_out' AND YEAR(movement_date) = 2020
    ) t ON a.account_id = t.account_id
    GROUP BY a.account_id, YEAR(t.transaction_date), MONTH(t.transaction_date)
)
SELECT
    account_id,
    2020 AS Year,
    ISNULL(SUM(CASE WHEN balance_month = 1 THEN (total_in - total_out) END), 0) AS Jan,
    ISNULL(SUM(CASE WHEN balance_month = 2 THEN (total_in - total_out) END), 0) AS Feb,
    ISNULL(SUM(CASE WHEN balance_month = 3 THEN (total_in - total_out) END), 0) AS Mar,
    ISNULL(SUM(CASE WHEN balance_month = 4 THEN (total_in - total_out) END), 0) AS Apr,
    ISNULL(SUM(CASE WHEN balance_month = 5 THEN (total_in - total_out) END), 0) AS May,
    ISNULL(SUM(CASE WHEN balance_month = 6 THEN (total_in - total_out) END), 0) AS Jun,
    ISNULL(SUM(CASE WHEN balance_month = 7 THEN (total_in - total_out) END), 0) AS Jul,
    ISNULL(SUM(CASE WHEN balance_month = 8 THEN (total_in - total_out) END), 0) AS Aug,
    ISNULL(SUM(CASE WHEN balance_month = 9 THEN (total_in - total_out) END), 0) AS Sep,
    ISNULL(SUM(CASE WHEN balance_month = 10 THEN (total_in - total_out) END), 0) AS Oct,
    ISNULL(SUM(CASE WHEN balance_month = 11 THEN (total_in - total_out) END), 0) AS Nov,
    ISNULL(SUM(CASE WHEN balance_month = 12 THEN (total_in - total_out) END), 0) AS Dec
FROM MonthlyBalances
GROUP BY account_id
UNION ALL
-- 补充没有任何交易的账户，确保所有账户都出现在结果中
SELECT
    account_id,
    2020 AS Year,
    0 AS Jan, 0 AS Feb, 0 AS Mar, 0 AS Apr, 0 AS May, 0 AS Jun,
    0 AS Jul, 0 AS Aug, 0 AS Sep, 0 AS Oct, 0 AS Nov, 0 AS Dec
FROM accounts
WHERE account_id NOT IN (SELECT DISTINCT account_id FROM MonthlyBalances)
ORDER BY account_id;

方案二：使用PIVOT运算符实现行列转换 #

WITH MonthlyBalances AS (
    SELECT
        a.account_id,
        YEAR(t.transaction_date) AS balance_year,
        DATENAME(MONTH, t.transaction_date) AS balance_month_name,
        (SUM(CASE WHEN t.transaction_type IN ('transfer_in', 'pix_in') THEN t.amount ELSE 0 END) -
         SUM(CASE WHEN t.transaction_type IN ('transfer_out', 'pix_out') THEN t.amount ELSE 0 END)) AS monthly_balance
    FROM accounts a
    LEFT JOIN (
        SELECT account_id, transfer_date AS transaction_date, amount, 'transfer_in' AS transaction_type FROM transfer_ins WHERE YEAR(transfer_date)=2020
        UNION ALL
        SELECT account_id, transfer_date AS transaction_date, amount, 'transfer_out' AS transaction_type FROM transfer_outs WHERE YEAR(transfer_date)=2020
        UNION ALL
        SELECT account_id, movement_date AS transaction_date, pix_amount AS amount, 'pix_in' AS transaction_type FROM pix_movements WHERE in_or_out='pix_in' AND YEAR(movement_date)=2020
        UNION ALL
        SELECT account_id, movement_date AS transaction_date, pix_amount AS amount, 'pix_out' AS transaction_type FROM pix_movements WHERE in_or_out='pix_out' AND YEAR(movement_date)=2020
    ) t ON a.account_id = t.account_id
    GROUP BY a.account_id, YEAR(t.transaction_date), DATENAME(MONTH, t.transaction_date)
)
SELECT
    account_id,
    2020 AS Year,
    ISNULL(Jan, 0) AS Jan,
    ISNULL(Feb, 0) AS Feb,
    ISNULL(Mar, 0) AS Mar,
    ISNULL(Apr, 0) AS Apr,
    ISNULL(May, 0) AS May,
    ISNULL(Jun, 0) AS Jun,
    ISNULL(Jul, 0) AS Jul,
    ISNULL(Aug, 0) AS Aug,
    ISNULL(Sep, 0) AS Sep,
    ISNULL(Oct, 0) AS Oct,
    ISNULL(Nov, 0) AS Nov,
    ISNULL(Dec, 0) AS Dec
FROM MonthlyBalances
PIVOT (
    SUM(monthly_balance)
    FOR balance_month_name IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
) AS PivotTable
UNION ALL
SELECT
    account_id,
    2020 AS Year,
    0 AS Jan, 0 AS Feb, 0 AS Mar, 0 AS Apr, 0 AS May, 0 AS Jun,
    0 AS Jul, 0 AS Aug, 0 AS Sep, 0 AS Oct, 0 AS Nov, 0 AS Dec
FROM accounts
WHERE account_id NOT IN (SELECT DISTINCT account_id FROM MonthlyBalances)
ORDER BY account_id;

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