代码之家 › 专栏 › 技术社区 › enkiki

带计数查询的MySQL案例

mysql

enkiki · 技术社区 · 7 年前

我以前也问过类似的问题,但我意识到我想要的输出是完全不同的。

我有以下疑问

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

4 回复 | 直到 7 年前

ScaisEdge 7 年前

你把从句放错了位置

  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

Eric 7 年前

改变你的 GROUP BY 到

GROUP BY (
    CASE
        WHEN LEVEL = 1 THEN "Bronze",
        etc...
        WHEN LEVEL = 10 THEN "Gold10"
    END
)

您的 SELECT

SELECT (
    CASE
        WHEN LEVEL = 1 THEN "Bronze",
        etc...
        WHEN LEVEL = 10 THEN "Gold10"
    END
) AS LEVEL
     , COUNT(*) AS total 
FROM admin_xxx
GROUP BY (
    CASE
        WHEN LEVEL = 1 THEN "Bronze",
        etc...
        WHEN LEVEL = 10 THEN "Gold10"
    END
)
ORDER BY total DESC LIMIT 10

Pietro Della Notte 7 年前

您还可以将级别/标签分离到另一个表,并可以将它们合并。例如:

SELECT levels.level_label, COUNT(*) AS total 
FROM admin_xxx
LEFT JOIN levels ON levels.label_value = admin_xxx.level
GROUP BY levels.level_label ORDER BY total DESC LIMIT 10

这是levels表:

id  level_label     label_value
1   Bronze          10
2   Silver          9
3   Gold            1
4   Gold2           2
5   Gold3           3
6   Gold4           4
7   Gold5           5
8   Gold6           6
9   Gold7           7
10  Gold8           8

Shushil Bohara 7 年前

试试这个: 显然你应该用 CASE 以以下方式实现所需的输出

SELECT CASE level 
        WHEN 1 THEN 'Bronze'
        WHEN 2 THEN 'Silver'
        WHEN 3 THEN 'Gold'
        WHEN 4 THEN 'Gold2'
        WHEN 5 THEN 'Gold3'
        WHEN 6 THEN 'Gold4'
        WHEN 6 THEN 'Gold5'
        WHEN 6 THEN 'Gold6'
        ELSE 'Unknown' END Level, 
    COUNT(*) AS total 
FROM admin_xxx
GROUP BY level 
ORDER BY total DESC LIMIT 10

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前