选择十大员工薪水 [英] Selecting Top 10 Employee Salaries

问题描述

我有一个员工薪水数据库.

I have a database for salaries of employees.

这是Salaries表字段:

emp_no INT(11)
salary INT(11)
from_date DATE
to_date DATE

我正在尝试获取前10名员工的薪水，而不是重复的员工.通过下面的代码，我可以获得emp_no的最高薪水，但是我的from_date和to_date默认为该emp_no的最早日期.

I'm trying to get the top 10 employee salaries, non-duplicate employees. With the code below I can get the maximum salary for the emp_no, but my from_date and to_date are defaulting to the earliest date values for that emp_no.

select emp_no, max(salary) as salary, from_date, to_date
from salaries
group by emp_no
order by salary DESC
limit 10

我假设GROUP BY将保留与薪水和emp_no相匹配的日期的值，但是经过一番思考，这样做是没有意义的.

I assumed GROUP BY would keep the values of the dates that matched up with the salary and emp_no, but after thinking about it a bit it doesn't make sense for it to do so.

非常感谢您提供有关如何获取正确日期值的帮助.

Any help with how to get the correct date values would be very much appreciated.

推荐答案

第一个表将包含前10名薪水.

First table will contain the top 10 salaries.

然后，您检查原始表以查看与emp_no和salary匹配的行以获取日期.

Then you check original table to see which row matches emp_no and salary to get dates.

SELECT S.*
FROM  
    (SELECT emp_no, max(salary) as salary
     FROM salaries
     GROUP BY emp_no
     ORDER BY salary DESC
     limit 10
   ) maxSal
INNER JOIN salaries S
   ON maxSal.emp_no = S.emp_no
  AND maxSal.salary = S.salary

这篇关于选择十大员工薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！