oracle select decode判断 ，sign使用例子

比较大小函数SIGN

sign(x)或者Sign(x)叫做符号函数，其功能是取某个数的符号（正或负）：当x>0，sign(x)=1;当x=0，sign(x)=0;当x<0， sign(x)=-1；

x可以是函数或计算表达式

有学生成绩表student,现在要用decode函数实现以下几个功能：成绩>85,显示优秀；>70显示良好；>60及格；否则是不及格。

假设student的编号为id,成绩为score,那么：
select id, decode(sign(score-85),1,'优秀',0,'优秀',-1, 
decode(sign(score-70),1,'良好',0,'良好',-1, 
decode(sign(score-60),1,'及格',0,'及格',-1,'不及格'))) 
from student；

decode 和 case when 使用原理都是一样的

SELECT  
             decode( sign(AllPerson.ID_-smallpct.HR_EFF_PPLAN_CYCLE_ID_),0,
             (AllPerson.AllPersonCount-smallpct.smallpctCount),
             AllPerson.AllPersonCount) AS noToReportNum
       
              from
           ( SELECT COUNT( pcpt1.HR_EFF_PPLAN_CYCLE_ID_) smallpctCount, pcpt1.HR_EFF_PPLAN_CYCLE_ID_  FROM
            HR_EFF_PPLAN_CYCLE cycle1,hr_eff_plan ep1 , HR_EFF_PFMCE_PLAN pp1,
            HR_EFF_PFMCE_PLANEECPT pcpt1 
            WHERE 
            ep1.STATUS_='0' AND cycle1.STATUS_='0' AND pp1.STATUS_='0' AND  pcpt1.STATUS_='0' 
             AND pcpt1.HR_EFF_PFMCE_PLAN_ID_=pp1.ID_ 
            AND pcpt1.HR_EFF_PPLAN_CYCLE_ID_=cycle1.ID_
             and to_char(sysdate, 'yy-MM')>=to_char(cycle1.OBJ_REPORT_STIME_,'yy-MM')
            AND cycle1.HR_EFF_PFMCE_PLAN_ID_=pp1.ID_ 
            AND pp1.HR_EFF_PLAN_ID_=ep1.ID_  GROUP BY pcpt1.HR_EFF_PPLAN_CYCLE_ID_ ) smallpct
        ,
        ( SELECT COUNT(news1.ID_) AllPersonCount ,cycle1.ID_  FROM
            HR_EFF_PPLAN_CYCLE cycle1,hr_eff_plan ep1 , hr_eff_news news1,HR_EFF_PFMCE_PLAN pp1
            WHERE 
            ep1.STATUS_='0' AND cycle1.STATUS_='0' AND pp1.STATUS_='0' 
            
             and to_char(sysdate, 'yy-MM')>=to_char(cycle1.OBJ_REPORT_STIME_,'yy-MM')
            AND cycle1.HR_EFF_PFMCE_PLAN_ID_=pp1.ID_ 
            AND pp1.HR_EFF_PLAN_ID_=ep1.ID_
            AND news1.HR_EFF_PLAN_ID_=ep1.ID_ GROUP BY cycle1.ID_ ) AllPerson
       

SELECT decode ( (COUNT(1)),0,2,(COUNT(1)) )  from      ( SELECT COUNT( pcpt1.HR_EFF_PPLAN_CYCLE_ID_) smallpctCount, pcpt1.HR_EFF_PPLAN_CYCLE_ID_  FROM
            HR_EFF_PPLAN_CYCLE cycle1,hr_eff_plan ep1 , HR_EFF_PFMCE_PLAN pp1,
            HR_EFF_PFMCE_PLANEECPT pcpt1 
            WHERE 
            ep1.STATUS_='0' AND cycle1.STATUS_='0' AND pp1.STATUS_='0' AND  pcpt1.STATUS_='0' 
             AND pcpt1.HR_EFF_PFMCE_PLAN_ID_=pp1.ID_ 
            AND pcpt1.HR_EFF_PPLAN_CYCLE_ID_=cycle1.ID_
             and to_char(sysdate, 'yy-MM')>=to_char(cycle1.OBJ_REPORT_STIME_,'yy-MM')
            AND cycle1.HR_EFF_PFMCE_PLAN_ID_=pp1.ID_ 
            AND pp1.HR_EFF_PLAN_ID_=ep1.ID_  GROUP BY pcpt1.HR_EFF_PPLAN_CYCLE_ID_ ) hasReport