查询各部门员工薪资的前2名与后2位数据

要实现每个部门薪资排名前2位和后2位的数据检索，我们可以利用SQL的窗口函数或子查询完成，以下是具体实现：

方法一：窗口函数筛选法 #

WITH ranked_salaries AS (
    SELECT 
        salary,
        deptno,
        ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY salary DESC) AS rank_top,
        ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY salary ASC) AS rank_bottom
    FROM employee_salaries
)
SELECT salary, deptno
FROM ranked_salaries
WHERE rank_top <= 2 OR rank_bottom <= 2
ORDER BY deptno, salary DESC;

逻辑说明 #

1. 通过ROW_NUMBER()窗口函数，按部门分组后分别生成薪资降序（取前2高）、升序（取前2低）的排名
2. 筛选出排名符合条件的记录，最后按部门和薪资降序整理结果

方法二：子查询合并法 #

如果不适用窗口函数，可通过UNION ALL分别查询最高、最低薪资后合并：

-- 取各部门薪资最高的2位
SELECT salary, deptno
FROM employee_salaries e1
WHERE (SELECT COUNT(*) FROM employee_salaries e2 WHERE e2.deptno = e1.deptno AND e2.salary > e1.salary) < 2
UNION ALL
-- 取各部门薪资最低的2位
SELECT salary, deptno
FROM employee_salaries e1
WHERE (SELECT COUNT(*) FROM employee_salaries e2 WHERE e2.deptno = e1.deptno AND e2.salary < e1.salary) < 2
ORDER BY deptno, salary DESC;

最终检索结果 #

基于提供的样本数据，执行上述查询后得到结果如下：

注：由于sales、tech部门均只有3条数据，中间薪资的记录会同时出现在前2和后2的结果中。

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