您可以使用 UNION ALL 以及 LEFT JOIN 有了它：

SELECT CONCAT(IFNULL(ranges.min, '∞'), '-', IFNULL(ranges.max, '∞')) AS `range`, avg(value) AS avg
FROM (
    SELECT 0 AS min, 21 AS max UNION ALL
    SELECT 21, 34 UNION ALL
    SELECT 34, 64 UNION ALL
    SELECT 64, NULL
) AS ranges
LEFT JOIN t ON (ranges.min IS NULL OR value >= ranges.min) AND
               (ranges.max IS NULL OR value <  ranges.max)
GROUP BY ranges.min, ranges.max

注意，上面的查询将 20.9999 内部 [0-21) 以及 21.0000 内部 [21-34) 射程。

您可以使用union获得所需的结果，如下所示：

select '0-21' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 0 and avggeneral <= 21
union
select '21-34' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 21 and avggeneral <= 34
union
select '34-64' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 34 and avggeneral <= 64
union
select '64+' as Range1, coalesce(avg(avggeneral),0) as AVG from Table1
where avggeneral > 64

此处为sql

你真的不需要用那些小数。
因为举例来说，如果“值”等于21，那么 CASE 在计算下一个之前已经返回0-21范围 WHEN .
但你还是需要在靶场上分组。
为了返回所有的范围，不管它们是否丢失，您可以将join留给一个包含这些范围的子查询。

SELECT Ranges.`range`, COALESCE(AVG(Q.`value`), 0) as `avg`
FROM
(
  SELECT 0 as `class`, ' 0-21' as `range`
  UNION ALL SELECT 21, '21-34'
  UNION ALL SELECT 34, '34-64'
  UNION ALL SELECT 64, '64+'
) Ranges
LEFT JOIN
(
  SELECT 
   `value`,
   case
   when `value` between  0 and 21 then 0
   when `value` between 21 and 34 then 21
   when `value` between 34 and 64 then 34
   when `value` > 64 then 64
   end as rangeclass
  FROM test
) Q ON Q.rangeclass = Ranges.`class`
GROUP BY Ranges.`class`, Ranges.`range`
ORDER BY Ranges.`class`

db<>在这里摆弄