oracle 如何在Oracle中产生排名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3379562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to produce rank in Oracle
提问by anirban
Need to rank the below by salary, with highest salary having rank 1.
需要按薪水排列以下，最高薪水有排名1。
The RANKcolumn shown is what I'm after:
显示的RANK列是我所追求的：
Empname sal address RANK
----------------------------------------------
Ram 3411 45,east road 2
Anirban 2311 34,west wind 4
Sagor 10000 34,south 1
Manisha 3111 12,d.h road 3
回答by OMG Ponies
Oracle10g means you can use analytic/ranking/windowing functions like ROW_NUMBER:
Oracle10g 意味着您可以使用 ROW_NUMBER 之类的分析/排名/窗口函数：
SELECT t.empname,
t.sal,
t.address,
ROW_NUMBER() OVER (ORDER BY t.sal DESC) AS RANK
FROM TABLE t
For the pedantic, replace ROW_NUMBERwith DENSE_RANKif you want to see ties get the same rank value:
对于迂腐，如果您想看到领带获得相同的排名值，请替换ROW_NUMBER为DENSE_RANK：
If two employees had the same salary, the RANKfunction would return the same rank for both employees. However, this will cause a gap in the ranks(ie: non-consecutive ranks). This is quite different from the dense_rank function which generates consecutive rankings.
如果两名员工的薪水相同，则RANK函数将为两名员工返回相同的等级。但是，这会造成排名的差距（即：非连续排名）。这与生成连续排名的dense_rank 函数完全不同。
The old school means of ranking is to use:
老派的排名方法是使用：
SELECT t.empname,
t.sal,
t.address,
(SELECT COUNT(*)
FROM TABLE x
WHERE x.sal <= t.sal) AS RANK
FROM TABLE t
The output will match the DENSE_RANK output -- ties will have the same rank value, while being numbered consecutively.
输出将匹配 DENSE_RANK 输出——关系将具有相同的等级值，同时连续编号。
回答by Piyush Mattoo
I often refer to this detailed, informative yet quick link Analytical Functionsfor the analytical functions.
我经常提及本详细丰富又快速链接解析函数的分析功能。
