用SQL找出前N名

来源：互联网发布：淘宝商品排名软件编辑：程序博客网时间：2024/04/28 16:45

业务系统中经常会有排名的需求，考试和比赛中则更普遍了。Excel 中也有个 Rank 函数供排名之用，数据库中更不例外了。假设需要找出工资最高的前三个员工工资（及其员工号）。不过，“前三名”的具体含义需要准确的定义，不然查出来的可能不是想要的结果。首先，因为表中记录数可能就少于三，查出来的记录可能等于三条也可能少于三条。其次，需要考虑并列名次的处理。

如果名次不能并列，则还需要考虑是否要根据工资之外的列来区分排名。例如：

     名次 ID 工资     -------------      1   80 1000      2   90 1000      3   13 900        取前三名，则“4   19 900”不会被输出。

如果名次可以并列，则还有两种不同方式。
1）名次并列，但是后续的名次要空出来。例如：
```
名次 ID 工资------------- 1   80 1000 1   90 1000 3   13 900
```
2）后续的名次不空出来。
```
名次 ID 工资------------- 1   80 1000 1   90 1000 2   13 900
```

在 Oracle 的示例数据库中，针对上述三种不同的情况可以通过不同的函数来实现。为了体现重复数据的影响，取了前5名。

用 ROW_NUMBER()。

SQL> select * from (select salary, row_number() over (order by salary desc) as seq from hr.employees) where seq <= 5;SALARY        SEQ   ---------- ---------- 24000          1 17000          2 17000          3 14000          4 13500          5

用 RANK()。

SQL> select * from (select salary, rank() over (order by salary desc) as seq from hr.employees) where seq <= 5;SALARY        SEQ   ---------- ---------- 24000          1 17000          2 17000          2 14000          4 13500          5

用 DENSE_RANK()。

SQL> select * from (select salary, dense_rank() over (order by salary desc) as seq from hr.employees) where seq <= 5;SALARY        SEQ   ---------- ---------- 24000          1 17000          2 17000          2 14000          3 13500          4 13000          5

第一种需要的结果相对简单一点，但是结果集有些特殊（工资相同的人可能只有一部分被选择出来）。在不支持 RANK() 的系统中，可以通过 LIMIT 等方式变通实现：

select employee_id, salary from hr.employee order by salary desc limit 5;

如果连 LIMIT 也没有，只允许采用最基本的 SQL 构造，则更麻烦和低效。基本思路是等价转换需要的查询“查工资的前五名”。例如：“工资的第1名”等价于“工资大于他的人为0人”，“工资的前5名”等价于“工资大于他的人不超过4人”。也可以转换为“找出所有工资大于等于工资排名第五的人”。不要忘了处理排名并列的情况。

SQL> select employee_id, salary from hr.employees o where (select count(distinct employee_id) from hr.employees where salary > o.salary) < 5 order by 2 desc, 1 desc;EMPLOYEE_ID     SALARY----------- ----------        100      24000        102      17000        101      17000        145      14000        146      13500

0 0