ShareCourse 學聯網

針對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講師 羅駿紘老師）