经典sql题目- 查找每个部门薪水前三的员工

题目描述

分析

关键词： 每个部门，前三 从上面两个关键词可以知道需要做两个操作，一是group by， 而是排序。 使用内置函数rank即可，两种写法，一是先rank，把rank的结果作为一张临时表。而是通过子查询的方式使用rank。

WITH RankedSalaries AS (
    SELECT 
        id,
        salary,
        departmentid,
        name,
        DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS rank
    FROM 
        Employee
)
SELECT 
    d1.name as Department,
    r1.name as Employee,
    r1.salary as Salary
FROM 
    RankedSalaries as r1,
    Department as d1
WHERE 
    r1.rank <= 3
    and r1.departmentid = d1.id
ORDER BY 
    r1.departmentid, r1.rank;

SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM 
    (SELECT 
         id, 
         name, 
         salary, 
         departmentid,
         DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS rank
     FROM Employee
    ) e
JOIN Department d ON e.departmentid = d.id
WHERE e.rank <= 3
ORDER BY e.departmentid, e.rank;

对比

场景	**用 CTE (WITH)	用子查询 (FROM (SELECT...))
数据量小（< 10,000 行）	子查询更快	✅ 更适合
数据量大（> 100,000 行）	CTE 可能更快	❌ 可能有性能问题
子查询会被多次使用	✅ CTE 更适合	❌ 可能导致重复计算
PostgreSQL 12+ 版本	CTE 可能被内联	两者差不多
PostgreSQL 11- 版本	CTE 更快（因为物化）	❌ 可能更慢