如何查询员工表中第4高及第N高薪资？附高效SQL方案

Alright, let's break this down clearly—first tackling the 4th highest salary, then generalizing to the Nth highest, with a focus on efficient, production-ready SQL.

First, let's address a key edge case: duplicate salaries. In most scenarios, we want the 4th distinct salary (e.g., if three employees all earn the 3rd highest salary, the 4th highest would be the next unique value below that).

Efficient Basic Solution #

This approach uses DISTINCT to eliminate duplicates, sorts salaries in descending order, and uses LIMIT/OFFSET to grab exactly the 4th entry:

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 3;

• DISTINCT: Removes duplicate salary values so we're only ranking unique amounts.
• ORDER BY salary DESC: Sorts salaries from highest to lowest.
• LIMIT 1 OFFSET 3: Skips the first 3 highest salaries (OFFSET 3) and returns the next 1 (the 4th highest).

Handling Edge Cases (No 4th Salary Exists) #

If the table has fewer than 4 distinct salaries, the query above returns nothing. To explicitly return NULL in this case, wrap it in IFNULL:

SELECT IFNULL(
    (SELECT DISTINCT salary
     FROM employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 3),
    NULL
) AS fourth_highest_salary;

To make this reusable for any N, we can create a custom function (works in MySQL; adjust syntax slightly for PostgreSQL/SQL Server) or use a dynamic approach.

Reusable Function (MySQL) #

This function takes an integer N and returns the Nth highest distinct salary:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    -- Offset is N-1 because OFFSET starts counting from 0
    SET N = N - 1;
    RETURN (
        SELECT DISTINCT salary
        FROM employee
        ORDER BY salary DESC
        LIMIT 1 OFFSET N
    );
END;

Usage: Call it like any other function:

SELECT getNthHighestSalary(4); -- Returns 4th highest salary

Handling Duplicate Ranks (Flexible Scenario) #

If your business logic requires treating identical salaries as the same rank (e.g., two employees with the 2nd highest salary mean the next salary is 3rd, not 3rd/4th), use the DENSE_RANK() window function:

SELECT salary AS nth_highest_salary
FROM (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employee
) ranked_salaries
WHERE salary_rank = 4
LIMIT 1;

• DENSE_RANK() assigns the same rank to identical salaries, so duplicates don't "skip" ranks.
• The subquery ranks all salaries, then we filter to get the 4th rank.

To make these queries run as fast as possible, follow these tips:

• Add an index on the salary column: CREATE INDEX idx_employee_salary ON employee(salary); This lets the database avoid a full table scan and use the sorted index to quickly locate the Nth salary.
• Avoid unnecessary columns: Only select salary (not all columns) to reduce data processing.
• Prefer LIMIT/OFFSET over window functions for large tables: Window functions like DENSE_RANK() require scanning and ranking every row in the table, while LIMIT/OFFSET can stop processing once it reaches the desired row (especially with an index).

内容的提问来源于stack exchange，提问作者Rahul Gupta