如何修正SQL查询以统计指定4个电影时长区间的计数？

你的查询出现区间重复的原因是GROUP BY子句用了runtime字段，这会让数据库按每一个具体的时长值单独分组，而不是按你定义的时长区间分组。

修正方法很简单，把GROUP BY的字段改成你用CASE生成的runtime_range即可：

SELECT 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END runtime_range,
      count(*) as Movie_Runtime
FROM movies
GROUP BY runtime_range;

如果使用的是不支持在GROUP BY中使用别名的数据库（如老版本MySQL），可以直接把CASE表达式放到GROUP BY里：

SELECT 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END runtime_range,
      count(*) as Movie_Runtime
FROM movies
GROUP BY 
      CASE WHEN runtime >= 0 AND runtime <= 60 THEN '0-60'
           WHEN runtime > 60 AND runtime <= 120 THEN '61-120'
           WHEN runtime > 120 AND runtime <= 180 THEN '121-180'
           ELSE 'Over 180'
      END;

这样就能得到每个时长区间的总计数，不会再出现重复的区间行。

内容的提问来源于stack exchange，提问作者Jeremy Wagerik