MySQL合并GROUP BY与SUM双列查询：交易表代理收支汇总需求

要把付款总额和收款总额的查询合并成一个，输出包含代理ID、付款总额、收款总额的汇总结果，这里提供两种实用的实现方法：

方法一：子查询+关联连接 #

先分别统计每个代理的付款、收款总额，再通过代理ID关联，确保所有有交易的代理都被覆盖（包括只付款或只收款的代理）：

SELECT
    COALESCE(p.agent_from, r.agent_to) AS agent_id,
    IFNULL(p.from_transactions, 0) AS 付款总额,
    IFNULL(r.to_transactions, 0) AS 收款总额
FROM
    (SELECT agent_from, SUM(amount) AS from_transactions FROM transactions GROUP BY agent_from) p
LEFT JOIN
    (SELECT agent_to, SUM(amount) AS to_transactions FROM transactions GROUP BY agent_to) r
ON p.agent_from = r.agent_to
UNION
SELECT
    COALESCE(p.agent_from, r.agent_to) AS agent_id,
    IFNULL(p.from_transactions, 0) AS 付款总额,
    IFNULL(r.to_transactions, 0) AS 收款总额
FROM
    (SELECT agent_from, SUM(amount) AS from_transactions FROM transactions GROUP BY agent_from) p
RIGHT JOIN
    (SELECT agent_to, SUM(amount) AS to_transactions FROM transactions GROUP BY agent_to) r
ON p.agent_from = r.agent_to;

注：如果你的数据库支持FULL OUTER JOIN（比如PostgreSQL、SQL Server），可以直接用全连接替代上面的UNION+左右连接写法，代码会更简洁。

方法二：UNION ALL统一格式后聚合 #

先把所有付款、收款记录转换成统一的代理ID+交易类型格式，再一次性统计两类总额，这种写法更简洁直观：

SELECT
    agent_id,
    SUM(CASE WHEN transaction_type = 'out' THEN amount ELSE 0 END) AS 付款总额,
    SUM(CASE WHEN transaction_type = 'in' THEN amount ELSE 0 END) AS 收款总额
FROM
    (SELECT agent_from AS agent_id, amount, 'out' AS transaction_type FROM transactions
     UNION ALL
     SELECT agent_to AS agent_id, amount, 'in' AS transaction_type FROM transactions) t
GROUP BY agent_id;

两种方法都能实现需求，方法二更推荐，代码量少且逻辑清晰，适合绝大多数场景。

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