如何统计CASE表达式生成的Pace字段各值的COUNT数量？

直接在原有查询基础上，通过GROUP BY分组配合COUNT()函数即可实现每个Pace值的数量统计，具体SQL如下：

SELECT 
    CASE 
         WHEN tempo < 40 THEN '1'
         WHEN tempo >= 40 AND tempo < 50 THEN '2'
         WHEN tempo >= 50 AND tempo < 60 THEN '3'
         WHEN tempo >= 60 AND tempo < 70 THEN '4'
         WHEN tempo >= 70 AND tempo < 80 THEN '5'
         WHEN tempo >= 80 AND tempo < 90 THEN '6'
         WHEN tempo >= 90 AND tempo < 100 THEN '7'
         WHEN tempo >= 100 AND tempo < 110 THEN '8'
         WHEN tempo >= 110 AND tempo < 120 THEN '9'
         WHEN tempo >= 120 AND tempo < 130 THEN '10'
         WHEN tempo >= 130 AND tempo < 140 THEN '11'
         WHEN tempo >= 140 AND tempo < 150 THEN '12'
         WHEN tempo >= 150 AND tempo < 160 THEN '13'
         WHEN tempo >= 160 AND tempo < 170 THEN '14'
         WHEN tempo > 170 THEN '15'
         ELSE NULL
    END AS Pace,
    COUNT(*) AS Pace_Count
FROM unpopular_songs
GROUP BY Pace
ORDER BY Pace;

关键说明： #

• 保留原有CASE逻辑生成Pace字段，去掉了原代码中数值比较时的单引号（tempo为数值类型，与字符串比较可能引发隐式转换问题）
• COUNT(*)用于统计每个Pace分组下的记录总数，别名Pace_Count便于识别
• GROUP BY Pace按生成的Pace值分组，确保每个Pace值仅返回一条统计结果
• ORDER BY Pace让结果按Pace的数字顺序排列，提升可读性

内容的提问来源于stack exchange，提问作者Gavin Gayden