如何在SQL Server中区分借贷账户并统计账户收支总额

要统计每个账户的总入账（Total_Credit_Amount）和总出账（Total_Debit_Amount），可以通过以下几种SQL实现方式：

方法一：使用UNION ALL合并账户后聚合（通用所有SQL方言） #

这种方式先将每笔交易的入账、出账账户分别拆解为独立记录，再统一分组汇总，能覆盖所有出现过的账户：

WITH all_accounts AS (
    -- 提取所有入账账户及对应金额
    SELECT Credit_Account AS account, Amount AS credit_amount, 0 AS debit_amount
    FROM transactions
    UNION ALL
    -- 提取所有出账账户及对应金额
    SELECT Debit_Account AS account, 0 AS credit_amount, Amount AS debit_amount
    FROM transactions
)
SELECT
    account AS `Distinct Credit/Debit Acc`,
    SUM(credit_amount) AS Total_Credit_Amount,
    SUM(debit_amount) AS Total_Debit_Amount
FROM all_accounts
GROUP BY account
ORDER BY account;

方法二：条件聚合结合子查询 #

和方法一逻辑类似，通过标记账户类型后聚合：

SELECT
    account AS `Distinct Credit/Debit Acc`,
    SUM(CASE WHEN account_type = 'credit' THEN amount ELSE 0 END) AS Total_Credit_Amount,
    SUM(CASE WHEN account_type = 'debit' THEN amount ELSE 0 END) AS Total_Debit_Amount
FROM (
    SELECT Credit_Account AS account, Amount, 'credit' AS account_type FROM transactions
    UNION ALL
    SELECT Debit_Account AS account, Amount, 'debit' AS account_type FROM transactions
) AS account_transactions
GROUP BY account
ORDER BY account;

方法三：全外连接聚合结果（适用于支持FULL OUTER JOIN的数据库） #

分别统计入账、出账账户的总额，再通过全外连接合并结果，确保无遗漏：

SELECT
    COALESCE(c.account, d.account) AS `Distinct Credit/Debit Acc`,
    COALESCE(c.total_credit, 0) AS Total_Credit_Amount,
    COALESCE(d.total_debit, 0) AS Total_Debit_Amount
FROM (
    SELECT Credit_Account AS account, SUM(Amount) AS total_credit
    FROM transactions
    GROUP BY Credit_Account
) c
FULL OUTER JOIN (
    SELECT Debit_Account AS account, SUM(Amount) AS total_debit
    FROM transactions
    GROUP BY Debit_Account
) d ON c.account = d.account
ORDER BY COALESCE(c.account, d.account);

说明 #

单独使用CASE语句容易失败的原因是：如果直接从原表按单一账户字段分组，会漏掉仅出账或仅入账的账户。上述方法通过合并所有账户维度，确保每个出现过的账户都被统计到，同时正确计算两类金额的总和。

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