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

列字段的处理 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;

语法：

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 ;

语法：

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

可以根据数据所处的层次结构实现自动的层次编号，例如：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”伪列判断某一个节点是根节点还是叶子节点，

如果此列返回的是数字0，则表示根节点，如果返回1，就是叶子节点。

示例：利用“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 ;

• < 上一篇

  PHP对于数据库的基本 操作

• 下一篇 >

  安全快速修改Mysql数据库名的5种方法