DECODE用法2

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.

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只能对数值型