Oracle中如何产生排名

发布于 2024-09-12 15:53:24 字数 367 浏览 6 评论 0原文

需要按薪水对下面的人员进行排名，最高薪水排名 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

原文

Need to rank the below by salary, with highest salary having rank 1.

The RANK column shown is what I'm after:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不必在意 2024-09-19 15:53:24

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 如果您想看到平局获得相同的排名值：

如果两名员工的工资相同，则 RANK 函数将返回相同的值两名员工的排名。 但是，这会导致排名出现差距（即：排名不连续）。这与生成连续排名的dense_rank函数有很大不同。

老式的排名方法是使用：

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

输出将与 DENSE_RANK 输出匹配 - 关系将具有相同的排名值，同时连续编号。

Oracle10g means you can use analytic/ranking/windowing functions like 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_NUMBER with DENSE_RANK if you want to see ties get the same rank value:

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.

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.

回复收藏 0 原文