sql - 如何从薪资表中找到第三或第n个最高薪水？(How to find third or nth maximum salary from salary table?)

Question

如何以优化方式从薪资table(EmpID,EmpName,EmpSalary)中找到third or nth最高薪水？

  ask by Karan Gandhi translate from so

Answer 1

Row Number :

(行号：)

SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

Sub Query :

(子查询：)

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

Top Keyword :

(热门关键字：)

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