Oracle 筆記之子查詢

子查詢

當我們的一個操作需要基于另一個查詢記過，那么就先行執(zhí)行的這個查詢就是子查詢

子查詢分為：

單行單列子查詢：查的結果只有一行，且只有一個字段

多行單列子查詢：查詢出來的結果有多行，但只有一列 多行多列子查詢

查詢出多行多個列。

通常，單行單列與多行多列子查詢用于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;

--查詢比本部門平均薪水高的員工的信息

--這里的思路是，我們應當先統(tǒng)計每個部門的平均工資因為這個查詢結果是一個多行多列的，所以我們將

--其當做一張表來看待，然后使用EMP表與其關聯(lián)查詢即可。所以，多行多列子查詢一般用在FROM子句后。

--FROM中書寫的子查詢，一般稱為內(nèi)視圖

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;

--查詢比本部門平均薪水高的員工的信息

--這里的思路是，我們應當先統(tǒng)計每個部門的平均工資因為這個查詢結果是一個多行多列的，所以我們將

--其當做一張表來看待，然后使用EMP表與其關聯(lián)查詢即可。所以，多行多列子查詢一般用在FROM子句后。

--FROM中書寫的子查詢，一般稱為內(nèi)視圖

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;

--子查詢也可以出現(xiàn)在SELECT子句中，通常實現(xiàn)的效果是外連接效果，若emp表中deptno字段的值在進行關聯(lián)

--查詢dept表中沒有查詢數(shù)據(jù)時，那么該值顯示為null

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

--分頁，將所有記錄分批獲取，目的：加快查詢，減小系統(tǒng)資源消耗

--分頁至少需要，為記錄編號，以及排序

--編號：在ORALCE中可以使用過ROWNUM的偽列 ROWNUM本身不在表中，使用他作為一列

--值是源自表中查詢出來數(shù)據(jù)進行的編號，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這兩個職位看做一組，其他職位的看做另一組，統(tǒng)計這兩組的總人數(shù)，

--思路：將需要被看做一組，但值又不同的那些數(shù)據(jù)，我們使用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);

--按照部門分組，按照工資降序，產(chǎn)生組內(nèi)連續(xù)唯一的數(shù)字：

SELECT ename,deptno,sal,ROW_NUMBER()OVER

(PARTITION BY deptno ORDER BY sal DESC)rank FROM emp;--rank函數(shù)與ROW_NUMBER的區(qū)別在于，進行排序的字段若值相同

--且他們在同一組時，那么他們得到的數(shù)字是相同的，但是，在下面的數(shù)字會有跳躍，RANK會生成組內(nèi)不連續(xù)且不唯一的數(shù)字。

--DENSE_RANK()會產(chǎn)生一個連續(xù)唯一的

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

--集合操作中： 并集，將兩個集合中的所有元素集合成一個集合 普通并集與全并集。

--全并集：會產(chǎn)生重復元素，兩個集合都有的元素，會在合并后在新的集合中出現(xiàn)兩次。

--交集：新的集合中只保留兩個集合都有的元素

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

--差集

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;