从数据库中的列值生成直方图

假设我有一个数据库列'grade',如下所示:

|grade|
|    1|
|    2|
|    1|
|    3|
|    4|
|    5|

在SQL中有一个非平凡的方法来生成这样的直方图吗？

|2,1,1,1,1,0|

其中2表示1级出现两次,1表示等级{2..5}出现一次,0表示等级6根本不出现.

我不介意直方图是否每个计数一行.

如果这很重要,数据库是由perl CGI通过unixODBC/FreeTDS访问的SQL Server.

编辑:谢谢你的快速回复!如果我可以确定哪个直方图值属于哪个等级,则不存在不存在的值(如上例中的等级6)是可以的.

SELECT COUNT(grade) FROM table GROUP BY grade ORDER BY grade

没有验证它,但它应该工作.但是,它不会显示6s等级,因为它根本没有出现在表中...

使用临时表来获取缺少的值:

CREATE TABLE #tmp(num int)
DECLARE @num int
SET @num = 0
WHILE @num < 10
BEGIN
  INSERT #tmp @num
  SET @num = @num + 1
END


SELECT t.num as [Grade], count(g.Grade) FROM gradeTable g
RIGHT JOIN #tmp t on g.Grade = t.num
GROUP by t.num
ORDER BY 1

如果数据点很多，还可以将范围分组在一起，如下所示：

SELECT FLOOR(grade/5.00)*5 As Grade, 
       COUNT(*) AS [Grade Count]
FROM TableName
GROUP BY FLOOR(Grade/5.00)*5
ORDER BY 1

With GradeRanges As (
  SELECT FLOOR(Score/5.00)*5     As GradeFloor, 
         FLOOR(Score/5.00)*5 + 4 As GradeCeiling
  FROM TableName
)
SELECT GradeFloor,
       CONCAT(GradeFloor, ' to ', GradeCeiling) AS GradeRange,
       COUNT(*) AS [Grade Count]
FROM GradeRanges
GROUP BY GradeFloor, CONCAT(GradeFloor, ' to ', GradeCeiling)
ORDER BY GradeFloor