8子查询
<h4>认识子查询</h4>
<pre><code>-- 子查询可以出现在各个位置
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);--工资最小的员工的信息</code></pre>
<h4>在where子句中使用子查询</h4>
<pre><code>--子查询单选单列
SELECT * FROM emp WHERE sal <(SELECT sal FROM emp WHERE ename='ALLEN');
SELECT * FROM emp WHERE sal <(SELECT AVG(sal) FROM emp);
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='ALLEN') AND sal>(SELECT sal FROM emp WHERE empno='7521')
--子查询返回单选多列数据，这个知识点是之前没遇到过的，顺序不能乱
SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';
SELECT * FROM emp WHERE (job,mgr)=(SELECT job,mgr FROM emp WHERE empno='7566') AND empno<>'7566';
SELECT * FROM emp WHERE (job,to_char(hiredate,'yyyy'))=(SELECT job,to_char(hiredate,'yyyy')FROM emp WHERE ename='ALLEN');
--子查询返回单行多列数据：多行子查询操作符（IN、ANY、ALL）
SELECT * FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);
SELECT * FROM emp WHERE sal NOT IN (SELECT MIN(sal) from emp GROUP BY deptno);
SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp);--书里说in里有空值将不返回任何值，但是这里返回了。</code></pre>
<h4>ANY操作符，旧版本的SQL中有ANY了，后来的版本引入了SOME，功能类似</h4>
<ul>
<li>=ANY：功能类似in</li>
<li>>ANY：比集合中最小的结果还要大</li>
<li><ANY：比集合中最大的结果还要小
<pre><code>--随便测试的
SELECT * FROM emp WHERE sal=ANY(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);
SELECT * FROM emp WHERE sal>ANY(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);
SELECT * FROM emp WHERE sal<ANY(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);</code></pre>
<h4>ALL操作符：类似ANY的用法，意思有点相反</h4></li>
<li>
<>ALL:等价于NOT IN
</li>
<li>>ALL:比最大值还要大</li>
<li><ALL:比最小值还要小
<pre><code>SELECT * FROM emp WHERE sal<>ALL(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);
SELECT * FROM emp WHERE sal>ALL(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);
SELECT * FROM emp WHERE sal<ALL(select MIN(sal) FROM emp where job='MANAGER' GROUP BY deptno);</code></pre></li>
</ul>
<h3>空数据判断，</h3>
<pre><code>- EXISTS 、NOT EXISTS，对子查询中是否有数据返回做出判断，有为true，无为false，NOT EXISTS反之。
SELECT * FROM emp WHERE EXISTS (SELECT * FROM emp WHERE empno='99999');
SELECT * FROM emp WHERE NOT EXISTS (SELECT * FROM emp WHERE empno='99999');
SELECT * FROM emp WHERE EXISTS (SELECT * FROM emp);</code></pre>
<h3>HAVING 子句中使用子查询，返回的是单行单列数据</h3>
<pre><code>SELECT deptno 部门编号,COUNT(empno) 部门人数,AVG(sal) 平均工资 FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp )
--查询出每个部门平均工资最高的部门名称及平均工资
SELECT d.dname,ROUND(AVG(e.sal),2) FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY d.dname HAVING AVG(sal)=(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);</code></pre>
<h3>在 FROM 子句中使用子查询。</h3>
<ul>
<li>
<p>子查询返回的是多行多列，多字段分组查询也能实现。子查询性能更强。</p>
<pre><code>--查询出每个部门的编号，名称，位置，部门人数，平均工资。
SELECT d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,e.count 部门人数,e.avg 平均工资 FROM dept d LEFT JOIN (SELECT deptno,COUNT(deptno) COUNT,round(AVG(sal),2) AVG FROM emp GROUP BY deptno) e ON e.deptno=d.deptno;
SELECT d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,count(e.empno) 部门人数,ROUND(AVG(e.sal),2) 平均工资 FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno GROUP BY d.deptno,d.dname,d.loc ;</code></pre>
</li>
<li>
<p>查询出所有在部门SALES(销售部)工作的员工的编号，姓名，基本工资，资金，职位，雇佣日期，部门的最高和最低工资。</p>
<pre><code>--确认已经知道的关联字段，如果没有就在子查询中创建关联字段
SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,d.max,d.min FROM emp e LEFT JOIN (SELECT deptno,MIN(sal)MIN,MAX(sal) MAX FROM emp GROUP BY deptno) d ON e.deptno=d.deptno WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');</code></pre>
</li>
<li>
<p>查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限。</p>
<pre><code>SELECT E.EMPNO 员工编号,
E.ENAME 员工姓名,
E.SAL 基本工资,
E.JOB 职位,
E.HIREDATE 雇佣日期,
D.DNAME 部门名称,
D.LOC 位置,
ME.ENAME 上级领导姓名,
S.GRADE 工资等级,
AE.COUNT 部门人数,
AE.AVG 平均工资,
AE.AVGYEARS 平均服务年限
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
LEFT JOIN EMP ME
ON E.MGR = ME.EMPNO
LEFT JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
LEFT JOIN (SELECT DEPTNO,
COUNT(DEPTNO) COUNT,
ROUND(AVG(SAL), 2) AVG,
ROUND(AVG(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12), 2) AVGYEARS
FROM EMP
GROUP BY DEPTNO) AE
ON E.DEPTNO = AE.DEPTNO
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP)</code></pre>
</li>
<li>
<p>列出薪金比 ALLEN 或 CLARK 多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名，部门人数。:薪金比 ALLEN 或 CLARK 多的所有员工，并不等价于比两人最低工资高。</p>
<pre><code>--对于select后不能再出现统计函数，可以在from中使用，再找到公共字段，联合查询
--错误
SELECT E.EMPNO 员工编号,
E.ENAME 姓名,
E.SAL 基本工资,
D.DNAME 部门名称,
ME.ENAME 领导姓名,
DC.COUNT 部门人数
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
LEFT JOIN EMP ME
ON E.MGR = ME.EMPNO
LEFT JOIN (SELECT DEPTNO, COUNT(DEPTNO) COUNT FROM EMP GROUP BY DEPTNO) DC
ON E.DEPTNO = DC.DEPTNO
WHERE E.SAL > (SELECT MIN(SAL)
FROM EMP
WHERE ENAME = 'CLARK'
OR ENAME = 'ALLEN')</code></pre>
</li>
<li>
<p>正确，结果也是一样，可能有问题，没有过滤掉自己。</p>
<pre><code>SELECT E.EMPNO 员工编号,
E.ENAME 姓名,
E.SAL 基本工资,
D.DNAME 部门名称,
ME.ENAME 领导姓名,
DC.COUNT 部门人数
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
LEFT JOIN EMP ME
ON E.MGR = ME.EMPNO
LEFT JOIN (SELECT DEPTNO, COUNT(DEPTNO) COUNT FROM EMP GROUP BY DEPTNO) DC
ON E.DEPTNO = DC.DEPTNO
WHERE E.SAL > ANY (SELECT SAL FROM EMP WHERE ENAME IN ('CLARK', 'ALLEN'))</code></pre>
</li>
<li>列出公司各个部门的经理（假如每个部门只有一个经理，job为MANAGER）的姓名、薪金、部门名称、部门人数、部门平均工资。
<pre><code>SELECT E.ENAME 姓名,
E.SAL 薪金,
D.DNAME 部门名称,
C.COUNT 部门人数,
C.AVG 部门平均工资
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
LEFT JOIN (SELECT DEPTNO, COUNT(DEPTNO) COUNT, AVG(SAL) AVG
FROM EMP
GROUP BY DEPTNO) C
ON E.DEPTNO = C.DEPTNO
WHERE JOB = 'MANAGER'</code></pre></li>
</ul>
<h3>在select子句中使用子查询</h3>
<pre><code>--查询出公司每个部门的编号、名称、位置、部门人数、平均工资。（使用之前的from子句也是可以实现的）
SELECT DEPTNO 部门编号,
DNAME 名称,
LOC 位置,
(SELECT COUNT(DEPTNO) FROM EMP WHERE DEPTNO = D.DEPTNO) 部门人数,
(SELECT ROUND(AVG(SAL), 2) FROM EMP WHERE DEPTNO = D.DEPTNO) 平均工资
FROM DEPT D;</code></pre>
<h3>WITH 子句，一种定义临时表的操作方法</h3>
<pre><code>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.AVG 平均工资,
E.COUNT 部门人数
FROM DEPT D
LEFT JOIN E
ON D.DEPTNO = E.DNO;</code></pre>
<ul>
<li>查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称，显示的结果按照部门编号进行排序。
<pre><code>WITH E AS
(SELECT DEPTNO DNO, MAX(SAL) MAX FROM EMP GROUP BY DEPTNO)
SELECT EM.EMPNO 雇员编号,
EM.ENAME 姓名,
EM.JOB 职位,
EM.HIREDATE 雇用日期,
EM.SAL 工资,
EM.DEPTNO 部门编号,
D.DNAME 部门名称
FROM EMP EM
JOIN E
ON E.DNO = EM.DEPTNO
AND EM.SAL = E.MAX
LEFT JOIN DEPT D
ON EM.DEPTNO = D.DEPTNO
ORDER BY EM.DEPTNO ASC</code></pre></li>
</ul>
<h3>分析函数：这部分知识强大，过于难，下次学。</h3>
<p><code>SELECT deptno,ename,sal,SUM(sal) OVER(PARTITION BY deptno) SUM FROM emp ;</code></p>
<h3>行列转换，PIVOT():列换行,UNPIVOT()行换列</h3>
<ul>
<li>子查询：明确了PIVOT()函数操作的数据。</li>
<li>统计函数（列）：转换过程中设定其他列的显示方法。</li>
<li>FOR 转换列名称：将子查询中返回的数据指定转换为列。
<pre><code>-- 查询每个部门中各个职位的总工资
SELECT deptno,job,SUM(sal) FROM emp GROUP BY deptno,job ;
SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP) PIVOT(SUM(SAL) FOR JOB IN('PRESIDENT'
PRESIDENT_JOB,
'CLERK'
CLERK_JOB,
'SALESMAN'
SALESMAN_JOB,
'MANAGER'
MANAGER_JOB,
'ANALYST'
ANALYST_JOB))
ORDER BY DEPTNO;</code></pre></li>
<li>在使用PIVOT() 函数中增加XML显示，可利用ANY，PL/SQL好像不能显示
<pre><code>SELECT * FROM (SELECT deptno,job,sal FROM emp) PIVOT XML (SUM(sal) FOR job IN (ANY))ORDER BY deptno ;
SELECT *
FROM (SELECT JOB,
DEPTNO,
SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO) SUM_SAL,
MAX(SAL) OVER(PARTITION BY DEPTNO) AMX_SAL,
MIN(SAL) OVER(PARTITION BY DEPTNO) MIN_SAL
FROM EMP) PIVOT(SUM(SAL) FOR JOB IN('MANAGER' MANAGER_SAL,
'CLERK' CLERK_SAL,
'SALESMAN' SALESMAN_SAL,
'ANALYST' ANALYST_SAL,
'PRESIDENT' PRESIDENT_SAL))
ORDER BY DEPTNO;</code></pre></li>
<li>注意上面的那个和下面这 个的区别，上面的是以部门为单位的聚合函数，下面的是以转列后的聚合函数
<pre><code>SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP) PIVOT(SUM(SAL) AS SUM_SAL, MAX(SAL) AS SUM_MAX FOR JOB IN('MANAGER' AS
MANAGER_JOB,
'CLERK' AS
CLERK_JOB,
'SALESMAN' AS
SALESMAN_JOB,
'ANALYST' AS
ANALYST_JOB,
'PRESIDENT' AS
PRESIDENT_JOB))
ORDER BY DEPTNO;</code></pre></li>
</ul>
<p><strong> 穿插DDL语句，为表添加列，插入一条记录，这部分内容在9章与11章</strong></p>
<pre><code>ALTER TABLE emp ADD (sex VARCHAR2(10) DEFAULT '男');
SELECT * FROM emp ;
UPDATE emp SET sex='女'WHERE to_char(hiredate ,'yyyy')='1981';
COMMIT;</code></pre>
<ul>
<li>
<p>添加一列查询，只转两个职位</p>
<pre><code>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;</code></pre>
</li>
<li>UNPIVOT():将行转换为列</li>
<li>INCLUDE NULLS :列变行后保留空值</li>
<li>EXCLUDE NULLS:（默认）列变行后不保留空值</li>
</ul>
<pre><code>WITH TEMP AS
(SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP) PIVOT(SUM(SAL) FOR JOB IN('PRESIDENT' AS
PRESIDENT_JOB,
'CLERK' AS
CLERK_JOB,
'SALESMAN' AS
SALESMAN_JOB,
'MANAGER' AS
MANAGER_JOB,
'ANALYST' AS
ANALYST_JOB))
ORDER BY DEPTNO)
SELECT *
FROM TEMP UNPIVOT EXCLUDE NULLS (SAL_SUM FOR JOB IN(PRESIDENT_JOB AS 'PRESIDENT',
CLERK_JOB AS 'CLERK',
SALESMAN_JOB AS 'SALESMAN',
MANAGER_JOB AS 'MANAGER',
ANALYST_JOB AS 'ANALYST'))
ORDER BY DEPTNO;</code></pre>
<ul>
<li>包含空值
<pre><code>WITH TEMP AS
(SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP) PIVOT(SUM(SAL) FOR JOB IN('PRESIDENT' AS
PRESIDENT_JOB,
'CLERK' AS
CLERK_JOB,
'SALESMAN' AS
SALESMAN_JOB,
'MANAGER' AS
MANAGER_JOB,
'ANALYST' AS
ANALYST_JOB))
ORDER BY DEPTNO)
SELECT *
FROM TEMP UNPIVOT INCLUDE NULLS (SAL_SUM FOR JOB IN(PRESIDENT_JOB AS 'PRESIDENT',
CLERK_JOB AS 'CLERK',
SALESMAN_JOB AS 'SALESMAN',
MANAGER_JOB AS 'MANAGER',
ANALYST_JOB AS 'ANALYST'))
ORDER BY DEPTNO;</code></pre></li>
</ul>
<h3>设置数据层次：确定数据行之间关系结构的一种操作，如管理层、职员层。</h3>
<pre><code>/*
LEVEL ...
CONNECT BY [nocycle] prior 连接条件
[start with 开始条件]
level：可以根据数据所处的层次结构实现自动的层次编号，例如，1、2、3
connect by：连接条码
start with ：根节点数据的开始条件
*/
--观察分层的基本关系，lpad()第三个参数是空格
SELECT empno,LPAD('|-',LEVEL*2,' ')||ename empname,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;
SELECT empno,ename empname,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;
-- 加入connect_by_isleaf伪列，返回0是根节点，返回1是叶子节点
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 列：找出分层中的根节点,可能指定查询根节点的某个字段
SELECT empno,LPAD('|-',LEVEL*2,' ')||ename empname,mgr,LEVEL,connect_by_root empno AS root_name FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;</code></pre>
<p><strong>后面还有一大堆东西，不学了</strong></p>
<h3>本章总结</h3>
<ul>
<li>子查询：在完事的语句中嵌入小查询，可以在任何地方插入。</li>
<li>子查询返回的类型有：单行单列、单行多列、多行单列、多行多列。</li>
<li>学习了 IN、ANY、ALL 等 。</li>
<li>子查询、限定查询、多表查询、统计查询结合有一起可以完成复杂查询操作。</li>
<li>分析函数。</li>
<li>行列转换可以用复杂的SQL完成，也可以使用 PIVOT()、UNPIVOT()函数实现。</li>
<li>分层查询。 </li>
</ul>