MySQL 如何计算别名列上的聚合函数 SUM？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14217944/
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
How to calculate aggregate function SUM on an alias column?
提问by afriex
How to calculate aggregate function SUM on an alias column?
如何计算别名列上的聚合函数 SUM？
SELECT a.question_id,
a.level,
Count(a.question_id) AS rank,
Sum(rank) AS total
FROM logs AS a,
question AS b
WHERE a.question_id = b.q_id
AND a.level = '2'
GROUP BY a.question_id
ORDER BY rank DESC
回答by zessx
Simply wrap your reused alias with (SELECT alias) :
只需使用 (SELECT alias) 包装您重复使用的别名：
SELECT a.question_id,
a.level,
COUNT(a.question_id) AS rank,
SUM(SELECT(rank)) AS total
FROM logs AS a,
question AS b
WHERE a.question_id = b.q_id
AND a.level = '2'
GROUP BY a.question_id
ORDER BY rank DESC
回答by Gordon Linoff
The scoping rules of SQL do not allow you to use an alias in the same select. Although this seems unreasonable, it is to prevent confusions such as:
SQL 的范围规则不允许您在同一个select. 虽然这看起来不合理，但它是为了防止混淆，例如：
select 2*x as x, x+1
Which xdoes the second variable refer to?
这x并不第二个变量是指什么？
You can solve this problem by using a subquery:
您可以使用子查询解决此问题：
select t.*, Sum(rank) AS total
from (SELECT a.question_id, a.level, Count(a.question_id) AS rank,
FROM logs AS a join
question AS b
on a.question_id = b.q_id
WHERE a.level = '2'
GROUP BY a.question_id
) t
ORDER BY rank DESC
I also fixed your join syntax. The use of a comma to mean a cross joinwith restrictions in the whereclause is rather outdated.
我还修复了您的连接语法。cross join在where子句中使用逗号来表示带有限制的a已经过时了。
回答by PinnyM
It doesn't really make sense to do this unless you have a different grouping for SUM(rank)than you would for COUNT(a.question_id). Otherwise, the SUM will always be working on one row - which is the value of the result of COUNT. Moreover, you are asking for COUNT(a.question_id)where you have specified the GROUP BY clause to also use a.question_id. This is not going to return the results you are looking for.
它并没有真正意义要做到这一点，除非你有一个不同的分组SUM(rank)比你的COUNT(a.question_id)。否则，SUM 将始终在一行上工作 - 这是 COUNT 结果的值。此外，您要求在COUNT(a.question_id)何处指定 GROUP BY 子句也使用a.question_id. 这不会返回您正在寻找的结果。
If you clarify what your grouping would be for rank, a subquery could be made for this.
如果您澄清您的分组将用于什么rank，则可以为此进行子查询。
