mysql - 如何对查询中的计算列进行GROUP BY？

我正在尝试使用Case when语句根据条件标记数据。然后，我需要对类别进行分组，以便能够对它们进行计数。我无法使用临时表执行此任务。

SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <= 250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players

输出：

Over 250    10
201-250     50
176-200     3

等等

最佳答案

您可以在case子句中简单地使用相同的group by表达式：

SELECT CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <= 250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END AS weight_group,
       COUNT(*)
FROM benn.college_football_players
GROUP BY CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <= 250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END

关于mysql - 如何对查询中的计算列进行GROUP BY？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51538648/