SQL Fundamentals: 子查询 || 行列转换(PIVOT,UNPIVOT,DECODE)，设置数据层次(LEVEL...CONNECT BY)

SQL Fundamentals || Oracle SQL语言

子查询（基础）

1、认识子查询

2、WHERE子句中使用子查询

3、在HAVING子句中使用子查询

4、在FROM子句中使用子查询

5、在SELECT子句中使用子查询

6、WITH子句

子查询（进阶）

7、分析函数

8、行列转换

9、设置数据层次

八、行列转换

• pivot和unpivot函数是Oracle 11g增加的新函数，利用此函数可以实现行列转换操作
• 按照原始方式实现，使用通用函数中的DECODE()函数

列字段的处理 SQL ——Case：用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理 ——DECODE：多值判断，如果某一个列（或某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值

SQL> select deptno,sum(sal) from emp group by deptno; Select case when deptno=10 then 'ACCOUNTING' when deptno=20 then 'RESERCH' when deptno=30 then 'SALES' end, sum(sal) from emp group by deptno

DECODE()函数语法结构： decode (expression, search_1, result_1) decode (expression, search_1, result_1, search_2, result_2) decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n) decode (expression, search_1, result_1, default) decode (expression, search_1, result_1, search_2, result_2, default) decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) 比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。 select decode(deptno, 10, 'ACCOUNTING', 20, 'RESERCH', 30, 'SALES' ), sum(sal) from emp group by deptno

DECODE行转列

SQL> select job,ename,sal from emp where job='MANAGER'; JOB       ENAME             SAL --------- ---------- ---------- MANAGER   JONES            2975 MANAGER   BLAKE            2850 MANAGER   CLARK            2450 SQL> select job,decode(ename,'BLAKE',SAL) BLAKE,decode(ename,'JONES',SAL) JONES,decode(ename,'CLARK',SAL) CLARK from emp where job='MANAGER'; JOB            BLAKE      JONES      CLARK --------- ---------- ---------- ---------- MANAGER                    2975 MANAGER         2850 MANAGER                               2450 SQL> select job,sum(decode(ename,'BLAKE',SAL)) BLAKE,sum(decode(ename,'JONES',SAL)) JONES,sum(decode(ename,'CLARK',SAL)) CLARK from emp where job='MANAGER' group by job; JOB            BLAKE      JONES      CLARK --------- ---------- ---------- ---------- MANAGER         2850       2975       2450 SQL> select job,avg(decode(ename,'BLAKE',SAL)) BLAKE,max(decode(ename,'JONES',SAL)) JONES,min(decode(ename,'CLARK',SAL)) CLARK from emp where job='MANAGER' group by job; JOB            BLAKE      JONES      CLARK --------- ---------- ---------- ---------- MANAGER         2850       2975       2450 查询每个部门中各个职位的总工资 —— 按照部门编号及职位进行分组 SELECT deptno , job , SUM(sal) FROM emp GROUP BY deptno , job ; 查询每个部门中各个职位的总工资 —— 将多条工资统计信息放在一行上进行显示 SELECT deptno , SUM(DECODE(job, 'PRESIDENT' , sal , 0 )) PRESIDENT_JOB , SUM(DECODE(job, 'MANAGER' , sal , 0)) MANAGER_JOB , SUM(DECODE(job , 'ANALYST' , sal , 0 )) ANALYST_JOB , SUM(DECODE(job , 'CLERK' , sal, 0 )) CLERK_JOB , SUM(DECODE(job , 'SALESMAN' , sal , 0)) SALESMAN_JOB FROM emp GROUP BY deptno ;

DECODE()函数是ORACLE自己的特色，如没有DECODE()函数， 一般数据库利用SELECT子句使用子查询方式完成.

SELECT temp.dno,SUM(manager_job),SUM(clerk_job) FROM( SELECT deptno dno, (SELECT SUM(sal) FROM emp WHERE job='MANAGER' AND empno=e.empno) manager_job, (SELECT SUM(sal) FROM emp WHERE job='CLERK' AND empno=e.empno) clerk_job FROM emp e) temp GROUP BY temp.dno;

• 在Oracle 11g版本之后，专门增加了pivot和unpivot两个转换函数

pivot函数

语法：

SELECT * | 列 [别名] ... FROM 子查询 PIVOT ( 统计函数(列)s FOR 转换列名称 IN ( 内容1 [[AS] 别名] , 内容2 [[AS] 别名] , ... 内容n [[AS] 别名] ) ) [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC] ;

范例1：查询总工资，(注意该有逗号的地方有逗号，不该有的时候不要有)

SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

范例2：PIVOT函数还可以使用一个ANY变为XML数据显示：

SELECT * FROM (SELECT deptno, job,sal FROM emp) PIVOT XML( SUM(sal) FOR job IN(ANY) ) ORDER BY deptno;·

范例3、查询更多信息，总工资，最高，最低的工资，利用分析函数就可以了.（这里只有一个统计函数SUM(sal)）

SELECT * FROM ( SELECT job ,deptno , sal, SUM(sal) OVER(PARTITION BY deptno) sum_sal , MAX(sal) OVER(PARTITION BY deptno) max_sal , MIN(sal) OVER(PARTITION BY deptno) min_sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

范例4、使用多个统计函数（SUM(sal) l , MAX(sal) ），查询出每个部门不同职位的总工资，和每个部门不同职位的最高工资（仅对job字段实现分组）

SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

范例5、设置多个统计列（job, sex）

ALTER TABLE emp ADD(sex VARCHAR2(10) DEFAULT 'male'); UPDATE emp SET sex='female' WHERE TO_CHAR(hiredate,'yyyy')='1981'; COMMIT;

SELECT * FROM (SELECT deptno , job , sal , sex FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR (job, sex) IN ( ('MANAGER','男') AS manager_male_JOB , ('MANAGER','女') AS manager_female_JOB , ('CLERK','男') AS clerk_male_JOB , ('CLERK','女') AS clerk_female_JOB ) ) ORDER BY deptno ;

unpivot函数

语法：

SELECT * | 列 [别名] ... FROM 子查询 UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS]( 统计函数(列)s FOR 转换列名称 IN ( 内容1 [[AS] 别名] , 内容2 [[AS] 别名] , ... 内容n [[AS] 别名] ) ) [WHERE 条件(s)] [GROUP BY 分组字段1 , 分组字段2 , ….] [HAVING 过滤条件(s)] [ORDER BY 排序字段 ASC|DESC] ; INCLUDE NULLS：列变为行转换之后保留所有的null数据； EXCLUDE NULLS（默认）：列变为行转换之后不保留null数据。

范例1， EXCLUDE NULLS（默认）：列变为行转换之后不保留null数据。

WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB ) ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT ( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ;

范例2，显示所有数据 INCLUDE NULLS：列变为行转换之后保留所有的null数据；

WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB )    ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT INCLUDE NULLS( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ;

九、设置数据层次

LEVEL可以设置数据层次结构。

设置层次函数

• 层次查询是一种较为确定数据行之间关系结构的一种操作，

• 例如，在现实社会的工作之中一定会存在“管理层”、“职员层”这样的基本分层关系，在学校也会分为“教学管理层”、“教师层”、“学生层”这样三种层次结构。

• 在Oracle之中用户也可以利用其自身所带的工具实现这样的层次组织。

语法：

LEVEL ...

CONNECT BY [NOCYCLE] PRIOR 连接条件

[START WITH 开始条件]

LEVEL	可以根据数据所处的层次结构实现自动的层次编号，例如：1、2、3；
CONNECT BY	指的是数据之间的连接， 例如：雇员数据依靠mgr找到其领导，就是一个连接条件，其中NOCYCLE需要结合CONNECT_BY_ISCYCLE伪列确定出父子节点循环关系；
START WITH	根节点数据的开始条件；

分层的基本关系

SELECT empno,ename,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

使用LPAD处理一下LEVEL

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

CONNECT_BY_ISLEAF伪列(判断根节点、叶节点)

一颗树状结构之中，节点会分为两种：根节点、叶子节点， 用户可以利用“CONNECT_BY_ISLEAF”伪列判断某一个节点是根节点还是叶子节点， ，就是叶子节点。

示例：利用“CONNECT_BY_ISLEAF”判断某一个节点是根节点还是叶子节点 SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

CONNECT_BY_ROOT 列（判断某一字段在本次分层中的根节点数据名称）

CONNECT_BY_ROOT的主要作用是取得某一个字段在本次分层之中的根节点数据名称， 例如：如果按照领导层次划分，则所有数据的根节点都应该是KING。

SELECT empno,ename,mgr,LEVEL,LPAD('|-',LEVEL,' ') || ename empname，LEVEL, CONNECT_BY_ROOT ename FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;-------------从mgr为空这个值开始

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , CONNECT_BY_ROOT ename FROM emp CONNECT BY PRIOR empno=mgr START WITH empno=7566 ;

SYS_CONNECT_BY_PATH (列 , char)函数

利用“SYS_CONNECT_BY_PATH()”函数按照给出的节点关系，自动的将当前根节点中的所有相关路径进行显示

使用SYS_CONNECT_BY_PATH()函数取得节点路径信息 SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

去掉某一节点的显示

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr AND empno!=7698 START WITH mgr IS NULL ;

ORDER SIBLINGS BY 字段

在使用层次查询进行数据显示时，如果用户直接使用ORDER BY子句进行指定字段的排序，有可能会破坏数据的组成结构

破坏程序结构的显示 SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER BY ename ;

利用“ORDER SIBLINGS”保持层次关系

SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER siblings BY ename ;

CONNECT_BY_ISCYCLE伪列

在进行数据层次设计的过程之中，最为重要根据指定的数据列确定数据间的层次关系，但是有时候也可能出现死循环， 例如：KING的领导是BLAKE，而BLAKE的领导是KING就表示一个循环关系，为了判断循环关系的出现，在Oracle中也提供了一个CONNECT_BY_ISCYCLE伪列，来判断是否会出现循环，如果出现循环，则显示1，没有出现循环，则显示0。 同时如果要想判断是否为循环节点，则还需要“NOCYCLE”的支持。

判断循环 SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf , DECODE(CONNECT_BY_ISCYCLE , 0 , '【√】没有循环' , 1 , '〖×〗存在循环') iscycle FROM emp CONNECT BY NOCYCLE PRIOR empno=mgr START WITH empno=7839 ORDER siblings BY ename ;