在 MySql 中对 UNION 结果求和的简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2387734/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
a simple way to sum a result from UNION in MySql
提问by Itay Moav -Malimovka
I have a union of three tables (t1,t2,t3). Each rerun exactly the same number of records, first column is id, second amount:
我有一个由三个表 (t1,t2,t3) 组成的联合。每次重新运行完全相同数量的记录，第一列是 id，第二列是数量：
1 10
2 20
3 20
1 30
2 30
3 10
1 20
2 40
3 50
Is there a simplein sql way to sum it up to only get:
是否有一种简单的 sql 方式来总结它只得到：
1 60
2 80
3 80
回答by Jimmy
select id, sum(amount) from (
select id,amount from table_1 union all
select id,amount from table_2 union all
select id,amount from table_3
) x group by id
回答by zerkms
SELECT id, SUM(amount) FROM
(
SELECT id, SUM(amount) AS `amount` FROM t1 GROUP BY id
UNION ALL
SELECT id, SUM(amount) AS `amount` FROM t2 GROUP BY id
) `x`
GROUP BY `id`
I groupped each table and unioned because i think it might be faster, but you should try both solutions.
我将每个表分组并合并，因为我认为它可能更快，但您应该尝试两种解决方案。
回答by adharris
Subquery:
子查询：
SELECT id, SUM(amount)
FROM ( SELECT * FROM t1
UNION ALL SELECT * FROM t2
UNION ALL SELECT * FROM t3
)
GROUP BY id
回答by just_myles
Not sure if MySQL uses common table expression but I would do this in postgres:
不确定 MySQL 是否使用公共表表达式，但我会在 postgres 中这样做：
WITH total AS(
SELECT id,amount AS amount FROM table_1 UNION ALL
SELECT id,amount AS amount FROM table_2 UNION ALL
SELECT id,amount AS amount FROM table_3
)
SELECT id, sum(amount)
FROM total
I think that should do the trick as well.
我认为这也应该可以解决问题。
回答by Norberto Prosdocini Alcantara
Yes!!! Its okay! Thanks!!!! My code finishing:
是的！！！没关系！谢谢！！！！我的代码整理：
SELECT SUM(total)
FROM (
(SELECT 1 as id, SUM(e.valor) AS total FROM entrada AS e)
UNION
(SELECT 1 as id, SUM(d.valor) AS total FROM despesa AS d)
UNION
(SELECT 1 as id, SUM(r.valor) AS total FROM recibo AS r WHERE r.status = 'Pago')
) x group by id
