sql 查询出部门工资第二高的人

sql 查询出部门工资第二高的人

这是今天面试官的一个考题，数据结构如下

要求查询出每个部门分数第二高的人，虽然是一个很简单的表，但远没有我想的那样简单。我的思路如下

整体思路：排除掉每个部门分数最高的人，再求出部门分数最高的人。

1.查询出每个部门最高分
---先分组---再用max()函数查询出 每组(部门)分数最高的人
SELECT department,MAX(score)as score FROM User GROUP BY department

2.左连接形成如下的表:可见mdepar不为null 就是每个部门最高分数的人
SELECT * FROM User u LEFT JOIN (SELECT department as mdepar,MAX(score) as mscore FROM User GROUP BY department) tab
ON tab.mscore=u.score and tab.mdepar=u.department

3.排除掉每个部门分数最高的人，也就是上表中mdepar不为null的人
SELECT a.* FROM 
(SELECT * FROM User u LEFT JOIN (SELECT department as mdepar,MAX(score) as mscore FROM User GROUP BY department) tab
ON tab.mscore=u.score and tab.mdepar=u.department)a
where a.mscore is null 

4.再求出每组分数最高的人即可
SELECT MAX(a.score) as score,a.department FROM
(SELECT a.* FROM 
(SELECT * FROM User u LEFT JOIN (SELECT department as mdepar,MAX(score) as mscore FROM User GROUP BY department) tab
ON tab.mscore=u.score and tab.mdepar=u.department)a
where a.mscore is null )a
GROUP BY a.department

当时看到这道题并没觉得复杂，实际操作起来还真不简单，应该有更简单的方法。求大神指点！