如何从单列Amount计算借贷，按用户与账户聚合统计借贷总额？

现有数据表包含User_ID、Credit_Account、Debit_Account、AMOUNT四列，具体数据如下：

需求说明 #

需实现以下目标：

• 从AMOUNT列计算每个用户的总贷方、总借方金额；
• 按每个账户聚合对应的借贷金额（Credit_Account和Debit_Account可能存在重复账户）；
• 最终结果展示User_ID、Credit_Account及对应借贷金额、Debit_Account及对应借贷金额、原始AMOUNT。

原SQL的问题 #

尝试的SQL逻辑存在错误，原代码如下：

SELECT USER_ID , SUM(CR_total) as Total_Credit
FROM
(
        SELECT  USER_ID
             , Credit_Account
             , AMOUNT as CR_total
        FROM table  
        GROUP BY USER_ID
)CR

LEFT JOIN 
(SELECT USER_ID , SUM(DR_total) as Total_Debit
FROM
(
        SELECT USER_ID
             , Debit_Account
             , AMOUNT as DR_total
        FROM table
        GROUP BY DR.USER_ID
)DR
ON DR.USER_ID = CR.USER_ID
group by USER_ID
ORDER BY USER_ID

问题点：

1. 子查询中GROUP BY USER_ID却同时选择非聚合列Credit_Account/Debit_Account，会导致取值不确定；
2. 未实现按账户聚合借贷金额的逻辑，也未关联原始记录的账户信息；
3. 存在语法错误：右连接部分缺少闭合括号，GROUP BY DR.USER_ID中的DR别名未定义。

正确SQL实现方案 #

通过CTE先分别聚合每个账户的总贷方、总借方金额，再关联回原始数据表，得到每条记录对应的账户聚合值：

WITH account_credit AS (
    -- 聚合每个账户作为贷方时的总金额
    SELECT 
        Credit_Account AS account,
        SUM(AMOUNT) AS total_credit_amount
    FROM your_table_name
    GROUP BY Credit_Account
),
account_debit AS (
    -- 聚合每个账户作为借方时的总金额
    SELECT 
        Debit_Account AS account,
        SUM(AMOUNT) AS total_debit_amount
    FROM your_table_name
    GROUP BY Debit_Account
)
SELECT 
    t.User_ID,
    t.Credit_Account,
    ac.total_credit_amount AS `Credit Amount against CA`,
    ad_ca.total_debit_amount AS `Debit Amount against CA`,
    t.Debit_Account,
    ac_da.total_credit_amount AS `Credit Amount against DA`,
    ad.total_debit_amount AS `Debit Amount against DA`,
    t.AMOUNT
FROM your_table_name t
-- 关联当前Credit_Account的贷方总金额
LEFT JOIN account_credit ac ON t.Credit_Account = ac.account
-- 关联当前Credit_Account的借方总金额（该账户作为Debit_Account时的总额）
LEFT JOIN account_debit ad_ca ON t.Credit_Account = ad_ca.account
-- 关联当前Debit_Account的贷方总金额（该账户作为Credit_Account时的总额）
LEFT JOIN account_credit ac_da ON t.Debit_Account = ac_da.account
-- 关联当前Debit_Account的借方总金额
LEFT JOIN account_debit ad ON t.Debit_Account = ad.account
ORDER BY t.User_ID;

预期结果示例 #

执行上述SQL后，结果格式如下（以部分数据为例）：

注：若账户无对应借贷记录，对应金额列显示NULL，可通过COALESCE(列名, 0)替换为0。

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