待研究 case when 和decode

 假设有张学生成绩表(CJ)如下 
[姓名] [学科] [成绩] 
张三 语文 80 
张三 数学 86 
张三 英语 75 
李四 语文 78 
李四 数学 85 
李四 英语 78 

现有需求如下：
（1）要求统计分数段的人数。显示结果为：
[成绩]      [人数]
0<成绩<60      0 
60<成绩<80      3
80<成绩<100    3

with w as (select 'zhang' name, 'chinese' subject,80 score from dual
  union all
          select 'zhang' name, 'math' subject,86 score from dual
       
  union all
 
  select 'zhang' name, 'english' subject,75 score from dual
  union all
 
  select 'li' name, 'english' subject,78 score from dual
  union all
 
  select 'li' name, 'math' subject,85 score from dual
  union all
 
  select 'li' name, 'chinese' subject,78 score from dual
  )
 select w.*  from w

with w as (select 'zhang' name, 'chinese' subject,80 score from dual
  union all
          select 'zhang' name, 'math' subject,86 score from dual
       
  union all
  
  select 'zhang' name, 'english' subject,75 score from dual
  union all
  
  select 'li' name, 'english' subject,78 score from dual
  union all
  
  select 'li' name, 'math' subject,85 score from dual
  union all
  
  select 'li' name, 'chinese' subject,78 score from dual
  )
 select w.*  from w 

  select w.*, decode( score, score >=0 and score<60  ,'0-60',
     score>=60 and score<80, '60-80', score>=80 and score<100 , '80-100', else)  from w
这样不行

 

select dj,count(*) rs from(select xm,xk,case when cj>=0 and cj<60 then 'c' when cj>=60 and cj<80 then 'b' when cj>=80 and cj<=100 then 'a' end dj from cjb)
group by dj