假设一个类似于这个
question的场景.我想得到以下结果：
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的分数范围？
解决方法
尝试这个查询(也在
SQL Fiddle)：
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.
