Oracle排名rank() dense_rank() row_number() 分區partition by

首先感謝生菜肥羊同學提供的問題和良好的解決方案，再次感謝！

一個查詢需求，要查詢出每個部門中工資排在前2名的員工資訊

提供表結構和記錄如下，友善大家進行學習

員工表（employee）

ID	DEPTID	NAME	SALARY
1	1	劉德華	2000
2	1	張惠妹	2500
3	1	谷超	2500
4	2	王傑	2000
5	2	武力海	4000
6	3	張三	10000
7	3	李四	20000
8	3	王五	30000

填入記錄後，思考這個需求，每個部門我們很容易想到分組group by，前兩名，很容易想到是排序order by，容易想到用group by... order by ...來進行查詢，但是這個需求是要查詢出工資最大的前兩名，而不是工資最大的或是工資最小的員工資訊，因為group by deptid進行分組後，查詢出來的結果隻能是group by後面的字段，或是聚合函數（sum、max、min、count、avg等），查詢的粒度比較粗，不夠精細。就因為是前2名的資訊，導緻了不能很好的使用group by ... order by ...進行查詢，即使能夠查詢出結果，效率方面也不會非常令人滿意！故放棄這種解決方案

這裡介紹Oracle中的内置函數rank() over(partition by ...order by ...)來解決，rank() over排名函數，partition by分區函數，order by排序函數，特别注意rank() over()中一定要有order by子句，就是一定要在排序的基礎上進行排名

整個的意思是在分區、排序後的基礎上進行排名，這樣就能很好解決每個部門工資在前2名的需求了，語句如下

select *

  from (select e1.deptid,

               e1.name,

               e1.salary,

               rank() over(partition by e1.deptid order by e1.salary desc) rn

          from employee e1)

 where rn <= 2

要提出的一點是，如果出現重複的情況如何處理，大家從查詢結果看到了張惠妹和谷超都是排在了第一名，而沒有第二名，dense_rank()同樣是排名函數，與rank的差別是它能夠查詢并列後的下一名，換成dense_rank()的查詢結果是張惠妹和谷超都是排在了第一名，劉德華排在了第二名。從字面上也很容易了解，dense_rank意思是密集排名

另外在介紹函數row_number()分析函數，也可以用row_number()來代替rank()進行查詢，row_number()并不會出現并列的情況，即使上面的工資相同，它也會排出一個第一第二來

rank、dense_rank、row_number在SQL Server中同樣可以使用