[筆記分享-1] Oracle SQL Row Limiting Clause


針對Oracle Database Server,如果我們要達到Top-N查詢的效果,在Oracle Database 11g 版本之前,我們只能透過Inline View搭配rownum的效果操作,例如:
這隻SQL Statement將會回傳公司薪水最高的前5名員工資訊
select * from
(select employee_id,salary from employees order by salary desc)
where rownum <=5;
然而這樣的SQL 程式碼只能用在Oracle Database Server,如果同樣的語法在MS SQL Server上將不能執行成功,因為這樣的語法並不符合ANSI SQL的標準,它只是Oracle Database Server專屬的語法。
除此之外,在Oracle Database 11g 版本之前的做法,針對要求得薪水由高到低排名第6到第10名的這種Top-N查詢的需求,甚至要使用到集合運算子(SET Operators),例如:
select * from (select employee_id,salary from employees order by salary desc)
where rownum <=10
MINUS
select * from (select employee_id,salary from employees order by salary desc)
where rownum <=5;
好消息是,從Oracle Database 12c R1版本開始,我們可以使用row_limiting_clause 來開發符合ANSI SQL標準的Top-N查詢語法,語法如下:
SELECT …FROM …
[ WHERE … ]
[ ORDER BY … ]
[OFFSET offset { ROW | ROWS }]
[FETCH { FIRST | NEXT } [{ row_count | percent PERCENT }] { ROW | ROWS }
ONLY | WITH TIES }]
所以我們現在將上述兩個範例以使用row_limiting_clause的方式改寫,如下:
–求得公司薪水最高的前5名員工資訊
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
FETCH NEXT 10 ROW ONLY;
–求得薪水由高到低排名第6到第10名的員工資訊
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;

※ 補充說明:
(1)使用ROW or ROWS的效果是一樣的
(2)使用FIRST or NEXT的效果是一樣的
(3)–假設第7跟第8名是相同值,此時應該要使用WITH TIES取代ONLY)
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;
(本文轉自 OCP講師 羅駿紘老師) 

留言

這個網誌中的熱門文章

[說文解字-1] 什麼是Oracle、OCP證照?

[新資訊-1] Oracle的新數據庫使用AI來自我修復