sql将两列求和并将两列相加

Question

我有一个数据库，里面有乒乓球比赛的多个分数。要进行排名，我需要计算多个列。

我想把独特的轮次加起来，以了解人们参与的频率。我还想把所有获胜的游戏加起来。有时我们玩“超级回合”(SR)，获胜的游戏加倍。SR默认设置为1。

要知道总分，我想将两个结果(participation & total_games)相加。

我现在所拥有的：

$sql_user = $conn->query("
SELECT user_id, count(distinct round) as participation, 
sum(IFNULL(GAMES_WIN,0) * SR) AS total_games 
FROM tt_game group by user_id ORDER BY user_id ASC");

是否可以这样做：

$sql_user = $conn->query("
SELECT user_id, count(distinct round) as participation, 
sum(IFNULL(GAMES_WIN,0) * SR) AS total_games 
sum(participation + total_games) AS total_score
FROM tt_game group by user_id ORDER BY total_score DESC");

我该如何回应这些结果呢？非常感谢！

Answer 1

不能，您不能在SELECT子句本身中引用在SELECT子句中定义的别名。

您可以重复以下表达式：

SELECT
  user_id,
  count(distinct round) as participation, 
  sum(IFNULL(GAMES_WIN,0) * SR) AS total_games, 
  count(distinct round) + sum(IFNULL(GAMES_WIN,0) * SR) AS total_score
FROM tt_game 
GROUP BY user_id
ORDER BY total_score DESC;

或者使用子查询：

SELECT user_id, participation, total_games, participation + total_game AS total_score
FROM
(
  SELECT
    user_id,
    count(distinct round) as participation, 
    sum(IFNULL(GAMES_WIN,0) * SR) AS total_games
  FROM tt_game 
  GROUP BY user_id
) aggregated
ORDER BY total_score DESC;

Answer 2

另一个select怎么样？

我认为您需要另一个select来计算total_score。

尝试如下所示：

$sql_user = $conn->query("
SELECT t.user_id, t.participation, t.total_games, (participation + total_games) AS total_score
FROM (SELECT user_id, count(distinct round) as participation, sum(IFNULL(GAMES_WIN, 0) * SR) AS total_games
      FROM tt_game
      group by user_id
      ORDER BY total_score DESC) AS t
");