如何在不使用top和sub查询的情况下从表中获取第n个最高工资?

尝试CTE-公用表表达式:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   RowNum <= 5

这将按降序排列前五名的薪水-您可以使用 RowNumn 值,基本上从工资列表中检索任何部分。

有 other ranking functions 也可以在SQL Server中使用-例如 NTILE 这将把你的结果分成n个大小相等的组(尽可能接近),因此你可以创建10个这样的组:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, NTILE(10) OVER(ORDER BY SalaryAmount DESC) AS 'NTile'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   NTile = 1

这将把你的工资分成10组,大小相等的一组 NTile=1 是工资的“前10%”。

;with cte as(
Select salary,
row_number() over (order by salary desc) as rn
from salaries
)

select salary 
from cte 
where rn=@n

(或使用) dense_rank 代替 row_number 如果您想要第n个最高的不同薪资金额)

Select  * 
From    Employee E1 
Where
N = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary >= E1.Salary)

with cte as(
select VendorId,IncomeDay,IncomeAmount,
Row_Number() over ( order by IncomeAmount desc) as RowNumber
 from DailyIncome 
 )

 select * from cte
 where RowNumber=2

试试这个。

 SELECT * FROM
  (SELECT Salary,
    rownum AS roworder
  FROM (select distinct Salary from employer)
  ORDER BY Salary
  )
 where roworder = 6
  ;

它可以简单地做如下的第二高-

Select MAX(Salary) from employer where Salary NOT IN(Select MAX(Salary) from employer);

但对于第n个最高值,我们必须使用CTE(公共表表达式)。

试试这个。使用CTE很容易找到n个等级的项目 **

with result AS
( 
 SELECT  *,dense_rank() over( order by Salary) as ranks FROM Employee
) 
select *from RESULT Where ranks = 2

**

显示5分钟SAL EMP表。

SELECT * FROM (SELECT Dense_Rank () Over (ORDER BY Sal ASC) AS Rnk, Emp.* FROM Emp) WHERE
Rnk=5;

要找到第n个最高工资:
表名-EMP

        emplyee_id     salary
             1          2000
             2          3000
             3          5000
             4          8000
             5          7000
             6          2000
             7          1000

SQL查询->这里n是要找到的最高工资:

select salary from (select salary from Emp order by salary DESC LIMIT N) AS E order by ASC LIMIT 1;

如果有重复的条目

30000,
23000,
23000,
15000,
一万四千八百

然后上面所选的查询将不会返回正确的输出。

查找正确的查询,如下所示:

with salaries as
(
    select Salary,DENSE_RANK() over (order by salary desc) as 'Dense' 
    from Table_1
)
select distinct salary from salaries
where dense=3

 SELECT salery,name
 FROM employ
 ORDER BY salery DESC limit 1, OFFSET n

with CTE_name (salary,name)
AS
( row_num() over (order by desc salary) as num from tablename )
select salary, name from CTE_name where num =1;

这在Oracle中有效

为了找到第n个最高的工资,我们只考虑唯一的工资,最高的工资意味着没有比它高的工资,第二个最高的工资意味着只有一个工资比它高,第三个最高的工资意味着两个工资比它高,同样,第n个最高的工资意味着n-1的工资比它高。 好吧,你可以用 极限 关键字,提供分页 能力。您可以这样做:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1 

例:MySQL第二高薪,无子查询:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1

使用MS SQL Server的最高SAL:

select sal from emp where sal=(select max(sal) from emp)

第二高SAL:

select max(sal) from emp where sal not in (select max(sal) from emp)

如果我们需要找不到行数、排名、密集排名和子查询的第n个最高工资怎么办?

希望下面的查询能有所帮助。

从[dbo]中选择*。[test]按薪资描述排序

Emp_Id  Name    Salary  Department
 4      Neelu   10000   NULL
 2      Rohit   4000    HR
 3      Amit    3000    OPS
 1      Rahul   2000    IT



select B.Salary from TEst B join Test A
on B.Salary<=A.Salary 
group by (B.Salary)
having count(B.salary)=2

结果:4000,第二高。