Oracle中decode函數用法含義

標籤：oracle decode函數

DECODE(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值) 

DECODE(欄位或欄位的運算，值1，值2，值3）

這個函數啟動並執行結果是，當欄位或欄位的運算的值等於值1時，該函數返回值2，否則返回值3 

當然值1，值2，值3也可以是運算式，這個函數使得某些SQL語句簡單了許多 

該函數的含義如下：

 IF 條件=值1 THEN　　　　

     RETURN(返回值1)

 ELSIF 條件=值2 THEN　　　　

     RETURN(返回值2)　　　　

  ......

 ELSIF 條件=值n THEN　　　　

     RETURN(返回值n)

 ELSE　　　　

     RETURN(預設值)

 END IF

DECODE函數的常見用途：按欄位內容分組、按欄位內容排序、固定行轉列

1. 使用DECODE實現按欄位內容分組

有時候分組可能比較複雜，比如需要對某些欄位內容合并分組處理，這樣使用簡單的GROUP BY 就不行了，

但是使用CASE或DECODE就可以得到預期的結果

範例：

- 建立簡單的僱員及對應職位表

 DROP TABLE t1;

 CREATE TABLE t1

 ( 

 ID NUMBER(10),

 NAME VARCHAR2(10),

 JOB VARCHAR2(20)

 );

 INSERT INTO t1 VALUES(1,‘jack‘,‘VP‘);

 INSERT INTO t1 VALUES(2,‘tony‘,‘CEO‘);

 INSERT INTO t1 VALUES(3,‘merry‘,‘VP‘);

 INSERT INTO t1 VALUES(4,‘james‘,‘OPERATION‘);

 INSERT INTO t1 VALUES(5,‘linda‘,‘OPERATION‘);

 COMMIT;

現在需要分別統計VP及以上職位的人數、普通僱員的人數，這是使用簡單的GROUP BY JOB

是不行的，使用DECODE來實現就很簡單

SELECT DECODE(JOB,‘VP‘,‘VP_CEO‘,‘CEO‘,‘VP_CEO‘,‘OPERATION‘) JOB,

   COUNT(*) JOB_CNT

FROM t1

 GROUP BY  DECODE(JOB,‘VP‘,‘VP_CEO‘,‘CEO‘,‘VP_CEO‘,‘OPERATION‘);

結果如下：

JOB                   JOB_CNT
-----------            --------------
VP_CEO                        3
OPERATION                  2

2. 使用DECODE實現按欄位內容排序

在日常開發中可能碰到這樣的情況，比如一張表有ID，NAME欄位（ID為代理主鍵），

需要按NAME值指定定序（如NAME是字元型‘某某部分‘），那麼怎樣實現排序呢？

按欄位內容排序和按欄位內容指定動態列排序

 - 建立測試表

 DROP TABLE t2;

 CREATE TABLE t2  

 (

 ID NUMBER,

 DEPT_NAME VARCHAR2(10),

 REGION_ID NUMBER(10)

 );

 INSERT INTO t2 VALUES(1,‘deptA‘,12);

 INSERT INTO t2 VALUES(2,‘deptA‘,10);

 INSERT INTO t2 VALUES(3,‘deptA‘,9);

 INSERT INTO t2 VALUES(4,‘deptA‘,7);

 INSERT INTO t2 VALUES(5,‘deptB‘,12);

 INSERT INTO t2 VALUES(6,‘deptB‘,13);

 INSERT INTO t2 VALUES(7,‘deptB‘,22);

 INSERT INTO t2 VALUES(8,‘deptB‘,9);

 INSERT INTO t2 VALUES(9,‘deptC‘,8);

 INSERT INTO t2 VALUES(10,‘deptC‘,10);

 INSERT INTO t2 VALUES(11,‘deptC‘,11);

 COMMIT;

 - a. 按欄位內容排序

 需求：按部門DEPT_NAME排序（A->B->C），對於每個部門內部按地區REGION_ID升序

 分析：這裡的部門DEPT_NAME不是數字（varchar2），直接排序時不行的，

             如果能將DEPT_NAME的每個值轉為對應的數字，再排序就可以了。

 SELECT ID,DEPT_NAME,REGION_ID

 FROM t2

 ORDER BY DECODE(DEPT_NAME,

                                          ‘deptA‘,1,

                                          ‘deptb‘,2,

                                          3),

                       REGION_ID;  

 - b. 按欄位內容指定動態列排序

 需求：若DEPT_NAME為deptA，則按ID升序排列，否則按REGION_ID升序排序

 SELECT ID,DEPT_NAME,REGION_ID

 FROM t2

 ORDER BY DECODE(DEPT_NAME,

                                         ‘deptA‘,ID,

                                         REGION_ID); 

3. 使用DECODE實現固定行轉列

 - 簡單的員工工作統計表

 DROP TABLE t3;

 CREATE TABLE t3

 (

 STUDENT_NO NUMBER(10),

 STUDENT_NAME VARCHAR2(10),

 COURSE_TYPE VARCHAR2(10),

 COURSE_SCORE NUMBER(10)

 );

 INSERT INTO t3 VALUES(1,‘jack‘,‘english‘,80);

 INSERT INTO t3 VALUES(1,‘jack‘,‘chinese‘,90);

 INSERT INTO t3 VALUES(1,‘jack‘,‘math‘,85);

 INSERT INTO t3 VALUES(2,‘tony‘,‘english‘,70);

 INSERT INTO t3 VALUES(2,‘tony‘,‘chinese‘,95);

 INSERT INTO t3 VALUES(2,‘tony‘,‘math‘,80);

 COMMIT;

 實現行轉列：

 SELECT STUDENT_NAME,DECODE(COURSE_TYPE,‘english‘,COURSE_SCORE) FROM t3;

 分析這條語句，分組與不分組的差別在哪裡？

 SELECT STUDENT_NAME,

                  MAX(DECODE(COURSE_TYPE,‘english‘,COURSE_SCORE)) ENGLISH,

                  MAX(DECODE(COURSE_TYPE,‘chinese‘,COURSE_SCORE)) CHINESE,

                  MAX(DECODE(COURSE_TYPE,‘math‘,COURSE_SCORE)) MATH

  FROM t3

  GROUP BY STUDENT_NAME;

為什麼需要MAX？

因為要實現行轉列，按欄位分組，對DECODE中的非分組列必須要有分組函數，當然MIN、SUM

AVG等組函數也可以實現，MAX、MIN對任何類型都適用，SUM、AVG只能對數值型 

 

Oracle中decode函數用法含義