mysql - 在另一列中重用列的结果

Question

如果我有查询

SELECT id,name,SUM(investments) as total_investments, SUM(investments)/10 as per_ten_investment
FROM table1
GROUP BY name

有什么方法可以让我在计算 per_ten_investment 时使用 total_investments 吗？所以我可以用

代替上面的

SELECT id,name,SUM(investments) as total_investments, total_investments/10 as per_ten_investment
FROM table1
GROUP BY name

上面的结果导致“field list”中的 Unknown column 'total_investments' 错误

我遇到的问题要复杂得多，但归结为这一障碍。谢谢大家。

Answer 1

一个选项是使用子查询，然后您可以只引用求和字段。话虽如此，我认为您的查询没有任何问题:

SELECT id, name, total_investments, total_investments/10 per_ten_investment
FROM (SELECT id, name, SUM(investments) as total_investments
    FROM table1
    GROUP BY name) t

此外，根据您的数据，您可能希望将 id 字段添加到 group by。 Mysql 比其他的更宽容。