各位好！
查询学生成绩分组并显示姓名及成绩。
create table t2(t_name varchar2(30),t_score int );
insert into t2 values('a','59');
insert into t2 values('b','60');
insert into t2 values('c','79');
insert into t2 values('d','80');
insert into t2 values('e','63');
insert into t2 values('f','72');
insert into t2 values('g','99');
insert into t2 values('h','14');
insert into t2 values('i','84');
insert into t2 values('j','82');
insert into t2 values('k','40');
insert into t2 values('l','97');
分数如果<60 为不及格， >=60 <80 良好 ， >=80 <=100 优秀
想要等到的结果如下：
状态 姓名 分数
优先 g 99
优先 l 97
优先 i 84
优先 j 82
优先 d 80
良好 c 79
良好 f 72
良好 e 63
良好 b 60
不及格 a 59
不及格 k 40
不及格 h 14
还请告知！
------解决思路----------------------
select case when t_score >= 80 then '优秀'
when t_score >= 60 then '及格'
else '不及格'
end result ,
t_name , t_score
from t2
order by t_score desc ;
------解决思路----------------------
select (case
when t_score >= 80 and t_score <= 100 then
'优先'
when t_score >= 60 and t_score <= 80 then
'良好'
when t_score < 60 then
'不及格'
end) 状态,
t_name 姓名,
t_score 分数
from t2
order by t_score desc
------解决思路----------------------
select c.*, count(1) over(partition by c.result) rn
from (select case
when t_score >= 80 then
'优秀'
when t_score >= 60 then
'及格'
else
'不及格'
end result,
t_name,
t_score
from t2) c