MySQL CASE 与 COUNT 查询

Question

我之前问过类似的问题，但我意识到我想要的输出完全不同。

我有以下查询

SELECT LEVEL, COUNT(*) AS total FROM admin_xxx
GROUP BY LEVEL ORDER BY total DESC LIMIT 10

表格输出

Level  Total
4       27
3       24
7       19
5       10
8       9
6       7
1       5
2       3

现在我只想用文字而不是数字重命名实际关卡。

我想将以下内容作为输出

Level     Total
Bronze      27
Silver      24
Gold        19
Gold2       10
Gold3       9
Gold4       7
Gold5       5
Gold6       3

每 10 个级别都有一个单独的级别名称。

我尝试了以下但出现语法错误

SELECT LEVEL, COUNT(*) AS total FROM admin_xxx
CASE
WHEN LEVEL = 1 THEN "Bronze",
etc...
WHEN LEVEL = 10 THEN "Gold10"
  END as LEVEL

GROUP BY LEVEL ORDER BY total DESC LIMIT 10

Answer 1

from 子句的位置错误

  SELECT 
      CASE
        WHEN LEVEL = 1 THEN "Bronze" 

        WHEN LEVEL = 10 THEN "Gold10"
      END as  LEVEL
    , COUNT(*) AS total 
  FROM admin_xxx
  GROUP BY LEVEL ORDER BY total DESC LIMIT 10