sql-按范围分组以包括没有值的范围

尝试以下查询（也在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()

ranges

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,(SELECt max(score)/10 FROM scores)) AS t(ten))

对于

ranges