用SQL找出前N名

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

  1. 如果名次不能并列，则还需要考虑是否要根据工资之外的列来区分排名。例如：
	     名次 ID 工资
	     -------------
	      1   80 1000
	      2   90 1000
	      3   13 900

取前三名，则“4 19 900”不会被输出。

  1. 如果名次可以并列，则还有两种不同方式。

1）名次并列，但是后续的名次要空出来。例如：

	名次 ID 工资
	-------------
	 1   80 1000
	 1   90 1000
	 3   13 900

2）后续的名次不空出来。

	名次 ID 工资
	-------------
	 1   80 1000
	 1   90 1000
	 2   13 900

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

  1. 用 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
  1. 用 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
  1. 用 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
[ SQL ]
Written on June 1, 2004