求一统计分析SQL

Question

table:
班级   学生    分数
甲班    A           100
甲班    B           99
甲班    C           97
甲班    D           80
甲班    E           90
乙班    F          100
乙班    G           98
乙班    H           91
乙班    I           80
乙班    J           90求个SQL能得到以下结果：
班级  总学生数   分数100学生数    分数95~99学生数    分数95以下学生数
甲班   5               1                 2            2
乙班   5               1                 1            3

Answer 1

SELECT 班级 总学生数 SUM(DECODE(分数, 100, 1, 0)),
       SUM(CASE
             WHEN 分数 BETWEEN 95 AND 99 THEN
              1
             ELSE
              0
           END) 分数95 99学生数,
       SUM(CASE
             WHEN 分数 < 95 THEN
              1
             ELSE
              0
           END) 分数95以下学生数
  FROM TABLE

Answer 2

SELECT 班级 ,总学生数 ,SUM(DECODE(分数, 100, 1, 0)),
       SUM(CASE
             WHEN 分数 BETWEEN 95 AND 99 THEN
              1
             ELSE
              0
           END) 分数95 99学生数,
       SUM(CASE
             WHEN 分数 < 95 THEN
              1
             ELSE
              0
           END) 分数95以下学生数
  FROM TABLE

Answer 3

有小小问题啊，呵呵 总学生数 count(*) 并且要分组 group by 班级

Answer 4

呵呵，光把标题列出来了SELECT 班级，count(*) 总学生数, count(DECODE(分数, 100, 1, null)),
       count(CASE
             WHEN 分数 BETWEEN 95 AND 99 THEN
              1
             ELSE
              null   END) 分数95- 99学生数,
       count(CASE
             WHEN 分数 < 95 THEN
              1
             ELSE
              null           END) 分数95以下学生数
  FROM TABLE group by 班级。

Answer 5

谢了，茅塞顿开，之前忘了统计函数不会统计null值记录。