oracle select加判断,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