mysql - 如何在 SQL 的数学运算符中使用别名？

Question

首先，如果“别名”是错误的词，请指正我。我正在谈论使用 AS 运算符重命名该列。

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

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

那么我该如何解决这个问题以继续使用别名，而不是这样写:

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

感谢您的帮助!

Answer 1

不幸的是，您不能以这种方式使用列别名。它们仅在 SELECT 语句被处理后可用。但是，您可以在子查询或公用表表达式 (CTE) 中执行此操作。这是简单的子查询:

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

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