sql – group by在范围内包含没有值的范围

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

我可以使用选择的答案作为解决方案：

select t.range as [score range],count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 9 then ' 0- 9'
    when score between 10 and 19 then '10-19'
    else '20-99' end as range
  from scores) t
group by t.range

如何确保即使没有该范围内的结果,即将显示30-39的分数范围？

解决方法

WITH ranges AS (
    SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,ten*10 AS r_min,ten*10+9 AS r_max
      FROM generate_series(0,9) AS t(ten))
SELECT r.range,count(s.*)
  FROM ranges r
  LEFT JOIN scores s ON s.score BETWEEN r.r_min AND r.r_max
 GROUP BY r.range
 ORDER BY r.range;

编辑：

您可以通过将参数更改为generate_series()来轻松调整范围.可以使用以下结构来确保范围将始终涵盖您的分数：

SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,ten*10+9 AS r_max
  FROM generate_series(0,(SELECT max(score)/10 FROM scores)) AS t(ten))

范围CTE.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!