mysql - 在mysql中对多列求和并求和它们的别名

Question

是否可以sum列并再次求和？

select 
id,
sum(t2.col) as cola,
sum(t3.col) as colb,
sum(t4.col) as colc,
sum(cola + colb + colc) as total
from t1
left join t2 on t2.id = t1.t2fk
left join t3 on t3.id = t1.t3fk
left join t4 on t4.id = t1.t4fk
group by t1.id

像这样的东西

sum(cola + colb + colc) as total

标签： mysqlselect

Answer 1

您不能使用别名来做到这一点，但您可以通过重写 SELECT 子句来做到这一点，如下所示：

select 
id,
sum(t2.col) as cola,
sum(t3.col) as colb,
sum(t4.col) as colc,
sum(t2.col) + sum(t3.col) + sum(t4.col) as total
from t1
left join t2 on t2.id = t1.t2fk
left join t3 on t3.id = t1.t3fk
left join t4 on t4.id = t1.t4fk
group by t1.id

不会有性能损失（即 MySQL 不会“重新汇总”三列来生成总数）