查找公司第三高薪水的 SQL 查询

SQL query to find third highest salary in company

提问by Prateek Chaudhary

I need to write a query that will return the third highest salaried employee in the company.

我需要编写一个查询，该查询将返回公司中薪水第三高的员工。

I was trying to accomplish this with subqueries, but could not get the answer. My attempts are below:

我试图用子查询来完成这个，但无法得到答案。我的尝试如下：

select Max(salary)
from employees
where Salary not in

 (select Max(salary)
from employees
where Salary not in 

(select Max(salary)
from employees));

My thought was that I could use 2 subqueries to elimitate the first and second highest salaries. Then I could simply select the MAX()salary that is remaining. Is this a good option, or is there a better way to achieve this?

我的想法是我可以使用 2 个子查询来限制第一和第二高的工资。然后我可以简单地选择MAX()剩余的工资。这是一个不错的选择，还是有更好的方法来实现这一目标？

回答by Orel Eraki

The most simple way that should work in any database is to do following:

适用于任何数据库的最简单方法是执行以下操作：

SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 1 OFFSET 2;

Which orders employees by salary and then tells db to return a single result (1 in LIMIT) counting from third row in result set (2 in OFFSET). It may be OFFSET 3if your DB counts result rows from 1 and not from 0.

它按薪水对员工进行排序，然后告诉 db 返回从结果集中的第三行（OFFSET 中的 2）开始计数的单个结果（LIMIT 中的 1）。OFFSET 3如果您的数据库从 1 而不是从 0 计算结果行，则可能是这样。

This example should work in MySQL and PostgreSQL.

这个例子应该适用于 MySQL 和 PostgreSQL。

回答by sandeep sharma

You can get the third highest salary by using limit , by using TOP keyword and sub-query

您可以通过使用 limit ，通过使用 TOP 关键字和子查询获得第三高的薪水

1. TOPkeyword

   SELECT TOP 1 salary 
   FROM 
       (SELECT TOP 3 salary 
        FROM Table_Name 
        ORDER BY salary DESC) AS Comp 
   ORDER BY salary ASC
   

2. limit

   SELECT salary 
   FROM Table_Name 
   ORDER BY salary DESC 
   LIMIT 2, 1
   

3. by subquery

   SELECT salary  
   FROM 
       (SELECT salary 
        FROM Table_Name 
        ORDER BY salary DESC 
        LIMIT 3) AS Comp 
   ORDER BY salary 
   LIMIT 1;
   

1. TOP关键词

   SELECT TOP 1 salary 
   FROM 
       (SELECT TOP 3 salary 
        FROM Table_Name 
        ORDER BY salary DESC) AS Comp 
   ORDER BY salary ASC
   

2. 限制

   SELECT salary 
   FROM Table_Name 
   ORDER BY salary DESC 
   LIMIT 2, 1
   

3. 通过子查询

   SELECT salary  
   FROM 
       (SELECT salary 
        FROM Table_Name 
        ORDER BY salary DESC 
        LIMIT 3) AS Comp 
   ORDER BY salary 
   LIMIT 1;
   

I think anyone of these help you.

我认为这些中的任何一个都可以帮助您。

回答by MillaresRoo

You may try (if MySQL):

您可以尝试（如果是 MySQL）：

SELECT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;

This query returns one row after skipping two rows.

此查询在跳过两行后返回一行。

You may also want to return distinct salary. For example, if you have 20,20,10 and 5 then 5 is the third highest salary. To do so, add DISTINCTto the above query:

您可能还想返回不同的工资。例如，如果您有 20、20、10 和 5，那么 5 是第三高的薪水。为此，请添加DISTINCT到上述查询：

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;

回答by Aman Dhiman

SELECT idFROM tablenameORDER BY idDESC LIMIT 2 , 1

SELECT idFROM tablenameORDER BY idDESC LIMIT 2 , 1

This is only for get 3rd highest value .

这只是为了获得第三高的价值。

回答by joe mathews

SELECT Max(salary) 
FROM   employee 
WHERE  salary < (SELECT Max(salary) 
                 FROM   employee 
                 WHERE  salary NOT IN(SELECT Max(salary) 
                                      FROM   employee)) 

hope this helped you

希望这对你有帮助

回答by shadowjfaith

If SQL Server this could work

如果 SQL Server 这可以工作

SELECT TOP (1) * FROM
   (SELECT TOP (3) salary FROM employees ORDER BY salary DESC) T
ORDER BY salary ASC

As for your number of subqueries question goes it depends on your language. Check this for more information

至于您的子查询数量问题，这取决于您的语言。检查这个以获取更多信息

Is there a nesting limit for correlated subqueries in Oracle?

Oracle 中的相关子查询是否有嵌套限制？

回答by Pradeep R P

I found a very good explanation in

我找到了一个很好的解释

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

This query should give nthhighest salary

此查询应提供nth最高薪水

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
    SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)

回答by Joel

Some DBMS's don't allow you to run several nested queries. Here is a solution that only uses 1 nested query:

某些 DBMS 不允许您运行多个嵌套查询。这是一个仅使用 1 个嵌套查询的解决方案：

SELECT salary
FROM
(
   SELECT salary
   FROM employees
   ORDER BY salary
   LIMIT 3
) as TBL1
ORDER BY salary DESC
LIMIT 1;

It should give you the desired result. It first finds the 3 largest salaries, then selects the smallest of the three (or the third one if they are equal). Here is an SQLFiddle

它应该给你想要的结果。它首先找到 3 个最大的工资，然后选择三个中最小的（如果相等，则选择第三个）。这是一个SQLFiddle

回答by VIKAS KOHLI

SELECT * FROM employeeORDER BY salaryDESC LIMIT 1 OFFSET 2;

SELECT * FROM employeeORDER BY salaryDESC LIMIT 1 OFFSET 2;

回答by Nishikant Karale

WITH CTE AS ( SELECT Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM Employee ) SELECT salary FROM CTE WHERE RN = 3

WITH CTE AS (SELECT Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM Employee ) 从 CTE WHERE RN = 3 SELECT 工资