Oracle 笔记之子查询

子查询

当我们的一个操作需要基于另一个查询记过，那么就先行执行的这个查询就是子查询

子查询分为：

单行单列子查询：查的结果只有一行，且只有一个字段

多行单列子查询：查询出来的结果有多行，但只有一列 多行多列子查询

查询出多行多个列。

通常，单行单列与多行多列子查询用于where子句中而多行多列子查询用于

FROM子句中。

--查看和SCOTT相同职位的其他员工

SELECT ename,sal,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT')

AND ename <>'SCOTT';

--查看哪些员工工资高于平均水平

SELECT ename,sal FROM emp WHERE  sal>(SELECT AVG(sal) FROM emp);

--题目：查看公司中和SALESMAN相同部门的其他职位员工的信息

--第一步骤：

SELECT ename,deptno FROM emp WHERE job='SALESMAN';

--第二步骤：

SELECT  ename,job,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE

job='SALESMAN') AND job<>'SALESMAN';

--查看比20部门所有员工工资都高的其他员工

SELECT ename,sal,deptno FROM emp WHERE deptno =20;

SELECT ename,sal,deptno FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno =20);

EXISTS的作用， 当子查询中可以至少返回一条记录，那么表达式返回true，下面的例子表示：查看含有员工

的部门

SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT * FROM emp e WHERE d.deptno = e.deptno);

--查看最低薪水高于30号部门最低薪水的部门

SELECT deptno,MIN(sal)"最低薪水" FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal)FROM emp WHERE deptno=30);

--查看最低薪水高于30号部门最低薪水的部门

SELECT deptno,MIN(sal)"最低薪水" FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal)FROM emp WHERE deptno=30);

--去除重复项

SELECT DISTINCT deptno FROM emp;

--查询比本部门平均薪水高的员工的信息

--这里的思路是，我们应当先统计每个部门的平均工资因为这个查询结果是一个多行多列的，所以我们将

--其当做一张表来看待，然后使用EMP表与其关联查询即可。所以，多行多列子查询一般用在FROM子句后。

--FROM中书写的子查询，一般称为内视图

SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT AVG(sal) avg_sal,deptno FROM emp

GROUP BY deptno) x WHERE e.deptno = x.deptno AND e.sal>x.avg_sal;

--查看和SCOTT相同职位的其他员工

SELECT ename,sal,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT')

AND ename <>'SCOTT';

--查看哪些员工工资高于平均水平

SELECT ename "姓名",sal"工资" FROM emp WHERE  sal>(SELECT AVG(sal) FROM emp);

--题目：查看公司中和SALESMAN相同部门的其他职位员工的信息

--第一步骤：

SELECT ename,deptno FROM emp WHERE job='SALESMAN';

--第二步骤：

SELECT  ename,job,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE

job='SALESMAN') AND job<>'SALESMAN';

--查看比20部门所有员工工资都高的其他员工

SELECT ename,sal,deptno FROM emp WHERE deptno =20;

SELECT ename,sal,deptno FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno =20);

--EXISTS的作用， 当子查询中可以至少返回一条记录，那么表达式返回true，下面的例子表示：查看含有员工

--的部门

SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT * FROM emp e WHERE d.deptno=e.deptno);

--查看最低薪水高于30号部门最低薪水的部门

SELECT deptno,MIN(sal)"最低薪水" FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal)FROM emp WHERE deptno=30);

--去除重复项

SELECT DISTINCT deptno FROM emp;

--查询比本部门平均薪水高的员工的信息

--这里的思路是，我们应当先统计每个部门的平均工资因为这个查询结果是一个多行多列的，所以我们将

--其当做一张表来看待，然后使用EMP表与其关联查询即可。所以，多行多列子查询一般用在FROM子句后。

--FROM中书写的子查询，一般称为内视图

SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT AVG(sal) avg_sal,deptno FROM emp

GROUP BY deptno) x WHERE e.deptno = x.deptno AND e.sal>x.avg_sal;

--子查询也可以出现在SELECT子句中，通常实现的效果是外连接效果，若emp表中deptno字段的值在进行关联

--查询dept表中没有查询数据时，那么该值显示为null

SELECT e.ename,e.sal,(SELECT d.deptno FROM dept d WHERE d.deptno=e.deptno) deptno FROM emp e;

--分页，将所有记录分批获取，目的：加快查询，减小系统资源消耗

--分页至少需要，为记录编号，以及排序

--编号：在ORALCE中可以使用过ROWNUM的伪列 ROWNUM本身不在表中，使用他作为一列

--值是源自表中查询出来数据进行的编号，ORACLE自动生成该列的值

SELECT * FROM emp;

SELECT * FROM (SELECT ROWNUM rn,e.ename "姓名",e.job "工作",e.sal "工资" FROM emp e ORDER BY "工资" DESC) WHERE

rn BETWEEN 5 AND 10;

SELECT ename,job,sal,DECODE(job,

                  'MANAGER',sal*1.2,

                  'ANALYST',sal*1.1,

                  'SALESMAN',sal*1.05,sal

                  )bouns

                  FROM emp;

--将MANAGER与ANALYST这两个职位看做一组，其他职位的看做另一组，统计这两组的总人数，

--思路：将需要被看做一组，但值又不同的那些数据，我们使用DECODE将他们改为相同的值即可。

SELECT DECODE(job,'MANAGER','VIP','ANALYST','VIP','OPERATIONS') NAME, COUNT(*) FROM emp

GROUP BY DECODE(job,'MANAGER','VIP','ANALYST','VIP','OPERATIONS');

SELECT deptno,dname FROM dept ORDER BY DECODE(dname,'OPERATIONS',1,'ACCPOUNTING',2,'SALES',3);

--按照部门分组，按照工资降序，产生组内连续唯一的数字：

SELECT ename,deptno,sal,ROW_NUMBER()OVER

(PARTITION BY deptno ORDER BY sal DESC)rank FROM emp;--rank函数与ROW_NUMBER的区别在于，进行排序的字段若值相同

--且他们在同一组时，那么他们得到的数字是相同的，但是，在下面的数字会有跳跃，RANK会生成组内不连续且不唯一的数字。

--DENSE_RANK()会产生一个连续唯一的

SELECT ename,deptno,sal, DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

--集合操作中： 并集，将两个集合中的所有元素集合成一个集合 普通并集与全并集。

--全并集：会产生重复元素，两个集合都有的元素，会在合并后在新的集合中出现两次。

--交集：新的集合中只保留两个集合都有的元素

--差集：新的集合中只保存我有你没有的元素。

--差集

SELECT ename,job,sal FROM emp WHERE job ='MANAGER' MINUS SELECT ename,job,sal FROM emp WHERE sal>2500;

--普通并集

SELECT ename,job,sal FROM emp WHERE job ='MANAGER' UNION SELECT ename,job,sal FROM emp WHERE sal>2500;

--全并集

SELECT ename,job,sal FROM emp WHERE job ='MANAGER' UNION ALL SELECT ename,job,sal FROM emp WHERE sal>2500;

--交集

SELECT ename,job,sal FROM emp WHERE job ='MANAGER' INTERSECT SELECT ename,job,sal FROM emp WHERE sal>2500;

SELECT * FROM sales_tab;

SELECT year_id,month_id,day_id,SUM(sales_value) FROM SALES_TAB GROUP BY

GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))

ORDER BY year_id,month_id,day_id;