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

问题描述

最近在一次采访中，我被要求编写一个查询，我必须在不使用 TOP 和任何子查询的情况下从表中获取第 n 个最高薪水?

Recently in an interview I was asked to write a query where I had to fetch nth highest salary from a table without using TOP and any sub-query ?

我完全困惑，因为我知道实现它的唯一方法是同时使用 TOP 和子查询.

I got totally confused as the only way I knew to implement it uses both TOP and sub-query.

请提供解决方案.

提前致谢.

推荐答案

尝试 CTE - 通用表表达式:

Try a CTE - Common Table Expression:

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

这将按降序获取前 5 位薪水 - 您可以使用 RowNumn 值并基本上从薪水列表中检索任何切片.

This gets the top 5 salaries in descending order - you can play with the RowNumn value and basically retrieve any slice from the list of salaries.

SQL Server 中有其他排名函数也使用过 - 例如有 NTILE 它将您的结果分成 n 个相等大小的组(尽可能接近)，因此您可以例如像这样创建 10 个组:

There are other ranking functions available in SQL Server that can be used, too - e.g. there's NTILE which will split your results into n groups of equal size (as closely as possible), so you could e.g. create 10 groups like this:

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%"的薪水组.

This will split your salaries into 10 groups of equal size - and the one with NTile=1 is the "TOP 10%" group of salaries.

这篇关于如何在不使用 TOP 和子查询的情况下从表中获取第 n 个最高工资?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！