如何在SQL中的数学运算符中使用别名?

问题描述

首先，如果别名"是错误的单词，请纠正我.我正在谈论使用AS运算符重命名该列.

First of all, please correct me if "alias" is the wrong word. I am talkin about renaming the column with AS operator.

所以我正在尝试计算这样的平均值:

So I'm trying calculate an average like this :

SELECT
users.username AS player_name,
COUNT(*) AS total_games,
SUM(games.points) AS total_points,
(total_points / total_games) AS average_points

FROM games,
INNER JOIN users
ON games.player_id = users.id
GROUP BY games.player_id

(查询可能是错误的，只是一个简单的示例)

在此查询中，行

(total_points / total_games) AS average_points

出现错误:unknown column total_points

所以我该如何解决此问题以继续使用别名，而不是编写以下代码:

so how can I fix this to keep using the aliases, instead of writing this :

(SUM(games.points) / COUNT(*) ) AS average_points

感谢您的帮助！

推荐答案

我相当确定不可能使用这样的别名.您将不得不漫长"地做下去……

I'm fairly sure it's not possible to use aliases like that. You will have to do it the 'long' way...

(SUM(games.points) / COUNT(*) ) AS average_points

我想将此答案编辑为第二个 @jbeldock 的答案，作为我自此以来多次使用的解决方案.我发现自己写出公式并在查询的其他部分中重新使用它们，导致带有复制/粘贴部分的大型混乱查询.将您的Forumlas放在子查询中，使您可以在外部查询中使用其结果，并使事情变得更加优雅.

I wanted to edit this answer to second @jbeldock 's answer as a solution I have since used many times myself. I found myself writing out formulas and re-using them in other parts of queries, leading to big messy queries with copy/pasted sections. Putting your forumlas in a subquery allows you to use their results in the outer query and make things more elegant.

这篇关于如何在SQL中的数学运算符中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！