MySQL的:SUM 2页联合在一起的表(4×SUM在一个结果)
问题描述:
我必须进行选择,被联合在一个简单的查询...MySQL的:SUM 2页联合在一起的表(4×SUM在一个结果)
SELECT description_x, a, b, c, d from table a WHERE person_id = 1 UNION SELECT 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 | d row 2: description_y | e | f | g | h row 3: summary | a+e | b+f | c+g | d+h
...好吗?
非常感谢!
答
您可以添加另一个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支持的热膨胀系数(正如在评论中提到的,是在MySQL 8日起推出)。
+0
这正是我正在寻找的东西。非常感谢你! – dusty
答
select sum(a) as a,sum(b) as b,sum(c) as c,sum(d) as d from (
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
)T
+0
非常好,谢谢。 – dusty
c似乎与c有任何关系似乎很奇怪。 – Strawberry