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

缺乏、安全感 2022-03-27 09:22 678阅读 0赞

**这是今天面试官的一个考题，数据结构如下**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MjQ1NQ_size_16_color_FFFFFF_t_70]

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

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

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

![在这里插入图片描述][20190112225859599.png]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MjQ1NQ_size_16_color_FFFFFF_t_70 1]

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

![在这里插入图片描述][20190112230944862.png]

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

![在这里插入图片描述][20190112231136888.png]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MjQ1NQ_size_16_color_FFFFFF_t_70]: /images/20220327/0021c42ba2064754b967c832c9675a31.png
[20190112225859599.png]: /images/20220327/d5448b54a3a84c4194ae39f9db553eb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MjQ1NQ_size_16_color_FFFFFF_t_70 1]: /images/20220327/1f0e961820b74231a34ce63f457c97e0.png
[20190112230944862.png]: /images/20220327/ae2b0e0c34fd46bd8a35403579d514bc.png
[20190112231136888.png]: /images/20220327/7bf357e8753b4d668d5b23a5e94e7779.png