这是今天面试官的一个考题,数据结构如下
要求查询出每个部门分数第二高的人,虽然是一个很简单的表,但远没有我想的那样简单。我的思路如下
整体思路:排除掉每个部门分数最高的人,再求出部门分数最高的人。
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
当时看到这道题并没觉得复杂,实际操作起来还真不简单,应该有更简单的方法。求大神指点!