select id,first_name,decode(sign(current_credits-5)+sign(current_credits-10),-2,'a',-1,'a',0,'b',1,'b',2,'c') from students;
个人后来只想出这样的办法, 在表仲添加一个等级的字段degree,然后做了一个存储过程。 begin update students set degree='1' where current_credits<=5; commit; update students set degree='2' where current_credits between 5 and 10; commit; update students set degree='3' where current_credits between 10 and 15; commit; update students set degree='4' where current_credits between 15 and 20; commit; update students set degree='5' where current_credits》20; commit; end; 然后就可以用decode了,如我要统计各个成绩段的数量,就可以这样了: select decode(current_credits,'1','<5', '2','5 and 10', '3','10 and 15', '4','15 and 20', '5','>20'), count(*) from students group by current_credits; 大家认为如何,就是感觉如果我的划分登记变化的话,如果要统计5-15之间的话,又要重新写过程和查询语句了。
在表仲添加一个等级的字段degree,然后做了一个存储过程。
begin
update students set degree='1'
where current_credits<=5;
commit;
update students set degree='2'
where current_credits between 5 and 10;
commit;
update students set degree='3'
where current_credits between 10 and 15;
commit;
update students set degree='4'
where current_credits between 15 and 20;
commit;
update students set degree='5'
where current_credits》20;
commit;
end;
然后就可以用decode了,如我要统计各个成绩段的数量,就可以这样了:
select decode(current_credits,'1','<5',
'2','5 and 10',
'3','10 and 15',
'4','15 and 20',
'5','>20'),
count(*)
from students
group by current_credits;
大家认为如何,就是感觉如果我的划分登记变化的话,如果要统计5-15之间的话,又要重新写过程和查询语句了。