oracle子查询笔记

子查询就是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成一个复杂插叙你的功能。
复杂查询=限定查询+多表查询+统计查询+子查询
注： 子查询一定要加上括号
子查询返回结果分为四种形式：

单行单列：理解为单值数据
单行多列：返回一行数据中心多个列的内容
多行单列：返回多行记录中同一列的内容，相当于给出了一个操作范围；
多行多列：查询返回结果是一张临时表
子查询常见操作

where子句：

子查询返回结构一般都是单行单列，单行多列，多行单列

单行多列（同时满足）：

SELECT *
FROM emp
WHERE (job,sal)=(
    SELECT job,sal
    FROM emp
WHERE ename='SCOTT') AND ename<>'SCOTT' ;
多行单列(IN,ANY,ALL)
1. IN操作符
例如：查询出与每个部门中最低工资相同的全部雇员信息
select *
from emp
where sal in
(select min(sal)from emp group by deptno)
注意：如果在in中子查询返回的数据有null，那么不会有影响，如果在not in中子查询返回的数据有null，那么就表示不会有任何数据返回。

SELECT e.ename
FROM emp e
WHERE e.empno NOT IN (
      SELECT m.mgr
      FROM emp m) ;
2. any操作符
三种形式：
=any：表示与子查询中的每个元素进行比较，功能与in类似（然而<>any不等于not in,一张表数据全部返回无意义所在）

any :比子查询返回结果的最小值要大（包含了>=any）即大于最小值得所有数据

<any:比子查询返回结果的最大的要小（包含了<=any）即小于最大值得所有数据
注：oracle中some功能与any相同。
3. all操作符
三种用法：
<>all:等价于not in（但是=all并不等价于in，是没有任何数据的）

all:比子查询结果中最大值还要大（包含了>=all）

<all:比子查询结果中最小值还要小（包含了<=all）
空数据判断
在sql之中提供了一个exists结构用于判断子查询是否有数据返回，如果子查询中数据返回，则exists结构返回true，反之返回false。
SELECT * FROM emp
WHERE EXISTS(
  SELECT * FROM emp WHERE empno=9999) ;
SELECT * FROM emp
WHERE not EXISTS(
  SELECT * FROM emp WHERE empno=9999) ;
having子句：
子查询返回结构一般都是单行单列，同时为了使用统计函数操作
from子句：
一般返回多行多列，可以按照一张临时表操作

在having子句中使用子查询

having 一定是结合group by 一起使用的。
from子句使用子查询
from确定的是数据来源，行列的组合（多行多列，先做统计查询在做表关联）。
使用子查询和多字段分组实现应该选择哪一个？
多字段分组：一定会有笛卡尔积，数据量会增大。

子查询：多表关联的主表数量平方的数量。
总结:开发过程中子查询提高查询的性能。

操作步骤：
确定数据表及表中字段

确定表之间的关联字段
select子句中使用子查询
with子句中使用子查询
with创建临时表查询，可以绕开from子句

with e as(select * from emp)
select *from e;
查询每个部门的编号、名称、位置、部门平均工资、人数

WITH e AS (
    SELECT deptno dno , ROUND(AVG(sal),2) avg , COUNT(sal) count
    FROM emp
    GROUP BY deptno)
SELECT d.deptno,d.dname,d.loc,e.count,e.avg
FROM e , dept d
WHERE e.dno(+)=d.deptno ;
分析函数（也可以使用若干统计函数）
传统sql问题：
·计算运行总量，逐一累加当前行与其之前行的每行记录数据

·查找当前行数据占总数据的百分比

·分区显示，按照不同部门或者职位进行排列统计

·计算流动行的平均值
分析函数主要语法：
函数名称([参数,...])over(partition by 子句 字段，...[order by 子句 字段,...[asc|desc][nulls first|nullslast] [windowing子句])；

函数名称：类似于统计函数（count，sum等），但是在此时提供了更多的函数支持
over子句：为分析函数指明一个查询结果集，此语句在select子句中使用

partition by 子句：将一个简单的结果集分为N组（分区），而后按照不同的分组对数据进行统计。

order by子句：名曲指明数据在每个分组里的排列顺序，分析函数结果与排列顺序有关。

nulllsfirst|nullslast：表示返回数据行中包含null值是出现排列序列前还是尾。

windowing子句（代名词）:给出在定义变化的固定的数据窗口的方法，分析函数将对此数据进行操作。
1.函数名称函数名称([参数,...])over(partition by 子句 字段，...[order by 子句 字段,...[asc|desc][nulls first|nullslast] [windowing子句])；

2.函数名称([参数,...])over(partition by 子句 字段，...[order by 子句 字段,...[asc|desc][nulls first|nullslast] )；

3.函数名称([参数,...])over(partition by 子句 字段，...[order by 子句 字段,...[asc|desc][nulls first|nullslast] [windowing子句])；

4.函数名称([参数,...])over(partition by 子句 字段)；

5.函数名称([参数,...])over()；

6.函数名称([参数,...])over[order by 子句 字段,...[asc|desc][nulls first|nullslast] )；
使用partition子句（使用分区）
例如：计算部门总工资
select deptno,ename,sal ,
sum(sal) over (partition by deptno) sumsal
from emp
·分窗子句主要是用于定义一个变化或者固定的数据窗口的方法，主要用于定义分析函数在操作行的集合，分窗子句有两种实现方式：

实现一：值域窗（range window），逻辑偏移，当前分区之中当前行的前N行到当前行的记录集。

实现二：行窗（row window），物理偏移，以排序的结果顺序就散偏移当前行起始记录集。

·而如果想指定range与row的偏移量，可以采用如下几种排序列

range|rows 数字 preceding

range|rows  between unbounded preceding and current row

range|rows  between current row and unbounded following

·以上几种排列之中包含的概念如下：

preceding：主要是设置一个偏移量，这个偏移量可以是用户设置的数字，或者其他标记

between and：设置一个偏移量的操作范围；

unbounded preceding：不限制偏移量大小

current row ：表示当前行。

following：如果不写此语句表示使用上N行与当前行指定数据比较，如果编写此语句，表示当前行与下N行数据比较。

验证range子句（分组内偏移量以内的相加分为向上偏移和向下偏移）
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal range 300 preceding) sum
from emp;
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal range between 0 preceding and 300 following) sum
from emp;
匹配当前行：
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal range between 0 preceding and current row) sum
from emp;
unbounded操作（不设置范围）
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal range between unbounded preceding and current row) sum
from emp;
设置2行物理偏移（当前行与前两行，依次类推）
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal rows 2 preceding) sum
from emp;
设置查询行的范围（此时与部门分区求和是相同的）
select deptno,ename,sal ,
sum(sal)over( partition by deptno order by sal rows between unbounded preceding and unbounded following) sum
from emp;
sum，min，max，avg，count

例如：查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、部门的人数、平均工资、最高工资、最低工资、总工资
select * from(
select empno,ename,job,sal,deptno,
count(empno) over(partition by deptno) count,
round(avg(sal) over(partition by deptno))avg,
sum(sal) over(partition by deptno)sum,
max(sal) over(partition by deptno)max,
min(sal) over(partition by deptno)min
from emp) e
where e.empno='7369';
image.png
本程序由于需要针对于每一个部门找出整个部门的工资统计信息，所以可以利用分窗子句完成，而每一个分窗子句的范围应该是所有数据，所以可以使用“RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”作为范围限定。

SELECT e.empno , e.ename , e.sal , d.dname , d.loc ,
    ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal
          RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) avg_salary ,
    MAX(sal) OVER (PARTITION BY e.deptno ORDER BY sal
          RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max_salary ,
    MIN(sal) OVER (PARTITION BY e.deptno ORDER BY sal
          RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min_salary
FROM emp e,dept d
WHERE e.deptno=d.deptno ;
dense_rank()函数（标号顺延）
select  deptno,ename,sal,
dense_rank() over(partition by deptno order by sal) denserank，
rank() over(partition by deptno order by sal) rank
from emp
select  deptno,
max(sal) keep(dense_rank first order by sal desc) max,
min(sal) keep(dense_rank first order by sal desc) min
from emp
group by deptno;
注意：over()声明的是一个数据集合，first_value(列),last_value(列)，取得集合中的首行和尾行。

select  deptno,empno,ename,sal,
first_value(sal) over(partition by deptno  order by sal range between unbounded preceding and unbounded following) first,
last_value(sal) over(partition by deptno  order by sal range between unbounded preceding and unbounded following) last
from emp
lag(列名称[行数字][默认值]),lead(列名称[行数字][默认值]),
select  deptno,empno,ename,sal,
lag(sal,2,0) over(partition by deptno  order by sal ) lag,
lead(sal,2,0) over(partition by deptno  order by sal ) leag
from emp
where deptno=20
计算相对位置，分区五条记录，这些记录会按照1、0.8、0.6等的方式进行划分。
select deptno ,ename,sal,
cume_dist() over (partition by deptno order by sal) cume
from emp
where deptno in(10,20)
2.ntile()函数结果的划分操作
select deptno ,sal,
sum(sal) over (partition by deptno order by sal) sum,
ntile(3) over (partition by deptno order by sal) ntile
from emp
3. ratio_to_report()函数整体数据百分比显示
select deptno ,sum(sal),
round(ratio_to_report(sum(sal)) over(),5)*100 ||'%' rate
from emp
group by deptno
      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 ;
设置多个统计函数。查询出每个部门不同职位的总工资，和每个部门不同职位的最高工资
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 ;
设置多个统计列
UPDATE emp SET sex='女' 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 (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 ;
    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...

connect by[nocycle]prior 连接条件

[start with 开始条件]

语法组成：
level：可以根据数据所在的层次结构实现自动层序编号。

connect by：指的是数据之间的连接。

start with：根节点的开始条件。
观察分层的基本关系
FROM emp 
CONNECT BY PRIOR empno=mgr 
START WITH mgr IS NULL ;
利用“CONNECT_BY_ISLEAF”判断某一个节点是根节点还是叶子节点
使用“CONNECT_BY_ROOT”语句
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()函数取得节点路径信息
      DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf
FROM emp 
CONNECT BY PRIOR empno=mgr 
START WITH mgr IS NULL ;
去掉某一节点
      DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf
FROM emp 
CONNECT BY PRIOR empno=mgr AND empno!=7698
START WITH mgr IS NULL ;
破坏程序结构的显示
      DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf
FROM emp 
CONNECT BY PRIOR empno=mgr
START WITH mgr IS NULL 
ORDER BY ename ;
利用“ORDER SIBLINGS”保持层次关系
      DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '   叶子节点') isleaf
FROM emp 
CONNECT BY PRIOR empno=mgr
START WITH mgr IS NULL 
ORDER siblings BY ename ;
将KING的领导编号变为7698
      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 ;
学习是一辈子的事情。