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;