oracle函数之decode的使用

1147q8su7d

　　语法：
　　decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
　　decode(字段或字段的运算,值1,值2,值3) 
　　select decode(value,<if this value>,<return this value>) from table
　　这个函数运行的结果是，当字段或字段的运算的值等于值1时，返回值2，否则返回值3 ；
　　值1...值n也可以使表达式，这个函数使得某些sql语句简单了好多
　　使用方法1：比较大小
　　select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;
　　sign()函数根据某个值是0,正数,负数，分别返回0,1,-1
　　使用方法二：相当于IF-THEN- ELSIF-THEN-...ELSE-END IF
　　如果我们想统计一张表中男女生的数量，常规做法是：
　　select  count(1) from table_name where sex='男'
　　union
　　select  count(1)  from table_name where sex='女'
　　使用decode则只需要一句话：
　　select  sum(decode(sex,'男',1,0) as '男',decode(sex,'女',1,0) as '女') from table_name;
　　实例：

SELECT O1.ORG_NAME S_NAME,
O1.ORG_ID S_ID,            
SUM(DECODE(R1.REJECT_REASON, '1', 1, 0)) AS WZZH,
SUM(DECODE(R1.REJECT_REASON, '2', 1, 0)) AS AQCSBZ,
SUM(DECODE(R1.REJECT_REASON, '3', 1, 0)) AS WBGZXKSX,
SUM(DECODE(R1.REJECT_REASON, '4', 1, 0)) AS WXCAQJD,
SUM(DECODE(R1.REJECT_REASON, '5', 1, 0)) AS WAQFH,
SUM(DECODE(R1.REJECT_REASON, '6', 1, 0)) AS FSGCL,
SUM(DECODE(R1.REJECT_REASON, '7', 1, 0)) AS FDYCZ,
SUM(DECODE(R1.REJECT_REASON, '8', 1, 0)) AS YSTSD,
SUM(DECODE(R1.REJECT_REASON, '9', 1, 0)) AS TDSBGZ,
SUM(DECODE(R1.REJECT_REASON, '10',1, 0)) AS BHGGJ,
SUM(DECODE(R1.REJECT_REASON, '11',1, 0)) AS BHGSB,
SUM(DECODE(R1.REJECT_REASON, '12',1, 0)) AS CSTZZY,
SUM(DECODE(R1.REJECT_REASON, '13',1, 0)) AS JHJS,
SUM(DECODE(R1.REJECT_REASON, '14',1, 0)) AS QT      
FROM TOP_ORGANIZATION O1
LEFT JOIN (SELECT R.REJECT_REASON,
O2.ORG_CODE
FROM SP_SS_EMPLOYEE_REJECT R,TOP_ORGANIZATION O2
WHERE R.REPORT_DEPARTMENT_OID = O2.ORG_ID                 
) R1
ON R1.ORG_CODE LIKE O1.ORG_CODE||'%'
WHERE O1.PARENT_ORG_ID =
GROUP BY O1.ORG_ID,O1.ORG_NAME