sql - 如何在 Oracle 中产生排名

Question

需要按薪水对以下进行排序，薪水最高的排在第 1 位。

显示的 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

Answer 1

Oracle10g 意味着您可以使用像 ROW_NUMBER 这样的分析/排名/窗口函数:

SELECT t.empname,
       t.sal,
       t.address,
       ROW_NUMBER() OVER (ORDER BY t.sal DESC) AS RANK
  FROM TABLE t

为了迂腐，将 ROW_NUMBER 替换为 DENSE_RANK如果你想看到领带获得相同的排名值:

If two employees had the same salary, the RANK function 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.

老派的排名方式是使用:

SELECT t.empname,
       t.sal,
       t.address,
       (SELECT COUNT(*)
          FROM TABLE x 
         WHERE x.sal <= t.sal) AS RANK
  FROM TABLE t

输出将匹配 DENSE_RANK 输出——领带将具有相同的排名值，同时被连续编号。