MySQL:对两个 UNIONed 表求和(一个结果为 4x SUM)

Question

我必须在一个简单的查询中进行 SELECT、UNIONed...

SELECT description_x, a, b, c, d from table a WHERE person_id = 1UNIONSELECT description_y, e ,f ,g ,h from table b WHERE person_id = 1

... a, b, c, d, e, f, g, h 都是整数。我怎样才能对它们求和，使结果看起来像这样......

row 1: description_x |  a  |  b  |  c  |  drow 2: description_y |  e  |  f  |  g  |  hrow 3: summary       | a+e | b+f | c+g | d+h 

...请问？

非常感谢!

Answer 1

您可以向其中添加另一个 union 来进行聚合。

select description_x, a, b, c, d from table a where person_id = 1
union all
select description_y, e ,f ,g ,h from table b where person_id = 1
union all
select 'summary', sum(a), sum(b), sum(c), sum(d) from (
    select a, b, c, d from table a where person_id = 1
    union all
    select e ,f ,g ,h from table b where person_id = 1
) t

如果 MySQL 支持 CTE(如评论中所述，MySQL 8 及更高版本将支持 CTE)，我们就不需要再次重写相同的查询。