SQL面试题---topN问题

topN问题是SQL面试里经常考的一个问题，即如何取每组最大的N条记录。

 

这里摘取leetcode上的一道题，因为主要为了说明如何选取topN的记录，因此这里删掉了第二张表（不需要进行两表连接）。

 

题目：根据Employee表中的信息，找出每个部门工资前三高的员工信息（部门号，姓名，工资）

| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |

 

解法一：使用window function

因为这里需要选取的是前三高的工资，如果工资相同，那么排名并列，因此这里用的窗口函数为dense_rank()。

SELECT DepartmentId, Name, Salary,
FROM (SELECT *,
      DENSE_RANK() OVER (PARTTITION BY DepartmentId
      ORDER BY Salary DESC) AS rank
      FROM Employee)
WHERE rank<=3;

这里需要注意的是：因为where在select之前执行，因此如果直接使用rank进行条件筛选会报错。需要再嵌套一层选择子句，把之前的语句放入from里面，因为from是最先执行的，因此这样就不会报错。

 

使用窗口函数是比较轻松的解法，但是有些数据库不支持窗口函数，因此很可能面试官会问你，如果不用窗口函数，这题该怎么解？

 

解法二：使用关联子查询

SELECT DepartmentId, Name, Salary
FROM Employee
WHERE (SELECT COUNT(DISTINCT e.Salary)
       FROM Employee AS e
       WHERE e.DepartmentId = Employee.DepartmentId
       AND e.Salary > Employee.Salary) < 3；

这里的解题思路是，先使用关联子查询把相同部门的员工归在一起，然后条件筛选出比各个员工工资高的员工，如果这些筛选出的员工的数量小于3个，那就说明进行对比的这些员工排在前三位。比如，如果你排在你们部门第一位，那么就有0个人的工资比你高，如果你排在第三位，那么就有2个人的工资比你高。

 

可以看到，上面关联的两张表其实是相同的两张表，因此也可以用自连接来解题，但是由于此种方式没有关联子查询来得清晰，因此这里就不表了。