[筆記分享-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;
(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;
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;
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;
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;
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;
留言
張貼留言