在mysql中获得每个人的第二高薪水 [英] Get second highest salary for each person in mysql

问题描述

我们有如下表格

person_id  |    salary
   1       |     1500
   1       |     1000
   1       |      500
   2       |     2000
   2       |     1000
   3       |     3000
   3       |     2000
   4       |     3000
   4       |     1000

我们希望每个人的工资第二高.按每个人分组并获得第二高的工资.如下图

We want second highest salary for each person. grouping by each person and get second highest salary for person. like below

person_id  |    salary
   1       |     1000
   2       |     1000
   3       |     2000
   4       |     1000

提前致谢:)

推荐答案

通过使用聚合函数和自连接，你可以做一些类似

By using aggregate function and self join you could do something like

select a.*
from demo a
left join demo b on a.person_id = b.person_id
group by a.person_id,a.salary
having sum(a.salary < b.salary) = 1 /* 0 for highest 1 for second highest 2 for third and so on ... */

或者在sum

having sum(case when a.salary < b.salary then 1 else 0 end)  = 1

演示

注意这不会像一个人可能有 2 个相同的薪水那样处理关系，我假设一个人的每个薪水值都将与一个人的其他薪水值不同，以处理@juergen d 提到的这种情况的方法 d 会起作用带有附加 case 语句

Note This doesn't handle ties like a person may have 2 same salary values, i assume each salary value for a person will be different from other salary values for a person to handle such case approach mentioned by @juergen d will work with additional case statement

这篇关于在mysql中获得每个人的第二高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！