sql查询第n个最高&员工姓名最低的薪水 [英] sql query for nth highest & lowest salary with employee name

问题描述

我正在尝试用员工姓名查询薪水，但我无法获得结果。



我的查询是

I'm trying to query the salary with employee name but I'm unable to get results.

my query is

Select Min(Salary) as Highest_Salary From (Select Top 1 * From employee Order by Salary Desc) Employee

sql查询第n个最高&员工姓名最低工资？



提前感谢。

sql query for nth highest & lowest salary with employee name?

thanks in advance.

推荐答案

查询获得第n个最高工资

Query to get nth Highest Salary

SELECT TOP 1 salary,Name
FROM (
SELECT DISTINCT TOP n salary,Name
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

查询获得第n个最低工资

Query to get nth Lowest Salary

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary ASC) a
ORDER BY salary

假设您想获得获得第四高薪的员工姓名：

Let say you want to get the employee name who earns the 4th highest salary:

with cte (empname, salary)
as
(
select top 4 empname, salary from table1 order by salary desc
) select empname, salary from cte where salary =
(select min(salary) from cte)

这个想法首先按降序排序，然后选择前4位，然后得到这4位的最小值。

您应该尝试找到问题第二部分的答案。反过来。

The idea is first sort the salary by descending order, then pick the top 4, then get the min of these 4.
You should try to find the answer to the second part of your question. Do the reverse.

请试试这个。

Please try this.

select name,salary from employee where salary in((select max(salary) from employee) ,
(select min(salary) from employee))

谢谢。

thanks.

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