如何用单条MySQL语句按币种分组统计各代理交易总额

要一次性获取USD、VES、EUR三种币种各自的各agent交易总额，可通过UNION ALL将三个独立的统计查询合并，得到包含三种币种统计结果的数据集，且每个结果可通过币种标识区分。

对应的SQL语句如下：

SELECT a.agent, SUM(a.amount) AS total_amount, 'USD' AS currency
FROM transactions AS a 
INNER JOIN currency AS b ON b.id = a.currency_id
WHERE b.currency = 'USD'
GROUP BY a.agent

UNION ALL

SELECT a.agent, SUM(a.amount) AS total_amount, 'VES' AS currency
FROM transactions AS a 
INNER JOIN currency AS b ON b.id = a.currency_id
WHERE b.currency = 'VES'
GROUP BY a.agent

UNION ALL

SELECT a.agent, SUM(a.amount) AS total_amount, 'EUR' AS currency
FROM transactions AS a 
INNER JOIN currency AS b ON b.id = a.currency_id
WHERE b.currency = 'EUR'
GROUP BY a.agent

说明 #

• 用UNION ALL替代UNION，无需对结果去重，执行效率更高；
• 每个子查询新增currency字段，明确当前统计的币种，方便区分不同币种的结果；
• 确保每个子查询返回的列数、列类型完全一致，符合UNION ALL的语法要求。

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