Oracle - 子查詢、TOP - N

1 子查詢

sql 中查詢是可以嵌套的，一個查詢的結果可以作為另外一個查詢的條件、表。

1 SELECT    select_list
2 FROM    table
3 WHERE    expr operator
4          (SELECT    select_list
5          FROM    table);

理解子查詢的關鍵在於把子查詢當作一張表來看待，外層的語句可以把內嵌的子查詢返回的結果當成一張表使用，子查詢結果可以作為一個虛表被使用。註意，子查詢要用括弧括起來 。子查詢根據其返回結果可以分為單行子查詢和多行子查詢。

1.1 單行子查詢

當子查詢有單行時，可以取單行中的一個欄位形成單個值用於條件比較，特別是當子查詢結果為單行單列時，可以當成一個值用於比較。

1 -- 查詢雇員其薪資在雇員平均薪資以上的雇員
2 select *
3 from emp e
4 where e.sal > (select avg(e.sal) "AVGSAL" from emp e);

1.2 多行子查詢

多行子查詢返回的結果可以作為表使用，通常結合in、some/any、all、exists。

1 -- 查在雇員中有哪些人是管理者
2 select e.*
3 from emp e 
4 where e.empno in (select distinct e.mgr
5                   from emp e
6                   where e.mgr is not null);

1.3 from 後的子查詢

子查詢結果可以作為一張續表用於from後。

1 -- 每個部門平均薪水的等級
2 select vt0.deptno,vt0.avgsal,sg.grade
3 from (select e.deptno,avg(e.sal) "AVGSAL"
4       from emp e
5       group by e.deptno) VT0, salgrade sg
6 where vt0.avgsal between sg.losal and sg.hisal;

2 TOP - N

在 Oracle 中和 mysql 不一樣，沒有 top 關鍵字，只能通過 rownum 屬性作為篩選條件，實現取“前 N 個幾個”。

rownum：表示對查詢結果的數據集記錄的編號，從1開始。

1 -- 查詢前10名雇員
2 select e.*,rownum
3 from emp e
4 where rownum <= 10;

當我們要查詢前 6 - 10 名雇員的時候就出現了問題。rownum 後面接 “<”、“<=”時不會出現問題，但取的第一個結果不是 rownum = 1 時，就會出現結果為空集的問題。例如 rownum >= 1 結果正常，但是 rownum > 1 為空集。可以這麼理解，rownum 是對結果集進行編號，當程式執行where rownum > 1 時，把結果的第一行排除掉了，但是此時的結果集第一個記錄 rownum 由 2 變成了 1，於是繼續把這一條記錄也剔除掉，以此類推，最後所有數據都被剔除了。

要解決這個問題，採取的方法時將 rownum 固定住，也就是將原表和 rownum 合成一個新表，新表上 rn 欄位表示行數，在這個新表上再作判斷。

1 --求薪水最高的第6到10名雇員
2 SELECT * 
3 FROM (SELECT X.*, ROWNUM NUM 
4      FROM (SELECT E.* 
5           FROM EMP E 
6           ORDER BY E.SAL DESC) X) 
7 WHERE NUM BETWEEN 6 AND 10;