MYSQL查询以找到薪水第n高的所有员工 [英] MYSQL query to find the all employees with nth highest salary

问题描述

这两个表是薪金_雇员和雇员

The two tables are salary_employee and employee

employee_salary

salary_id  emp_id  salary

员工

emp_id |    first_name | last_name |    gender  | email | mobile  | dept_id  | is_active

查询以获取薪水排名第n位的所有员工，其中n = 1,2,3，...任何整数

Query to get the all employees who have nth highest salary where n =1,2,3,... any integer

SELECT  a.salary, b.first_name 
FROM    employee_salary a 
JOIN    employee b 
ON      a.emp_id = b.emp_id 
WHERE   a.salary = (
                      SELECT    salary 
                      FROM      employee_salary  
                      GROUP BY  salary 
                      DESC     
                      LIMIT 1 OFFSET N-1
                   )

我的问题:

1)有没有更好，更优化的方法可以查询此信息，

1) Is there any better and optimized way we can query this,

2)使用LIMIT是个不错的选择

2) Is using LIMIT an good option

3)我们还有更多选择来计算第n个最高薪水，这是最高薪水，应遵循的内容以及何时执行?

3) We have more options to calculate the nth highest salary, which is the best and what to follow and when?

使用以下一个选项:

SELECT *
   FROM employee_salary t1
   WHERE ( N ) = ( SELECT COUNT( t2.salary )
                   FROM employee_salary t2 
                   WHERE  t2.salary >=  t1.salary 
                 )

使用排名方法

SELECT salary
FROM
(
  SELECT @rn := @rn + 1 rn,
       a.salary
  FROM tableName a, (SELECT @rn := 0) b
  GROUP BY salary DESC
) sub
WHERE sub.rn = N

推荐答案

这个评论太长了.

您已经问过似乎是一个合理的问题.在SQL中有多种处理方法，有时某些方法比其他方法更好.排名问题只是众多示例之一.您的问题的答案"是，通常，order by在MySQL中的性能将优于group by.尽管这还取决于特定的数据以及您认为更好的数据.

You have asked what seems like a reasonable question. There are different ways of doing things in SQL and sometimes some methods are better than others. The ranking problem is just one of many, many examples. The "answer" to your question is that, in general, order by is going to perform better than group by in MySQL. Although even that depends on the particular data and what you consider to be "better".

该问题的具体问题是您有三个不同的查询，它们返回了三个不同的东西.

The specific issues with the question are that you have three different queries that return three different things.

第一个返回所有具有相同密集等级"的员工.该术语是有目的使用的，因为它对应于MySQL不支持的ANSI dense_rank()函数.因此，如果您的薪水是100、100和10，它将返回两行，排名为1，第一行为2.

The first returns all employees with a "dense rank" that is the same. That terminology is use purposely because it corresponds to the ANSI dense_rank() function which MySQL does not support. So, if your salaries are 100, 100, and 10, it will return two rows with a ranking of 1 and one with a ranking of 2.

如果有平局，第二个返回不同的结果.如果薪水分别为100、100、10，则此版本将不返回排名为1的行，排名为2的行和排名为3的行.

The second returns different results if there are ties. If the salaries are 100, 100, 10, this version will return no rows with a ranking of 1, two rows with a ranking of 2, and one row with a ranking of 3.

第三个返回完全不同的结果集，即薪水和薪水排名.

The third returns an entirely different result set, which is just the salaries and the ranking of the salaries.

我的评论旨在尝试对您的数据进行查询.实际上，您应该从功能和性能的角度决定您真正想要的东西.

My comment was directed at trying the queries on your data. In fact, you should decide what you actually want, both from a functional and a performance perspective.

这篇关于MYSQL查询以找到薪水第n高的所有员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！