top2的薪水排名_牛客题霸

假设有一个employees表，里面记录了公司里职工的信息。包括职工id、职工姓名、部门、薪水、入职日期。请编写一个SQL查询，找到每个部门里排名top2的薪水信息，返回结果按照部门和薪水降序排列。示例输出如下：

示例1

输入

drop table if exists employees;
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);



INSERT INTO employees (employee_id, employee_name, department, salary, hire_date)
VALUES
    (1, 'John Doe', 'Sales', 5000.00, '2022-01-01'),
    (2, 'Jane Smith', 'Sales', 5000.00, '2022-02-01'),
    (3, 'Smith', 'Sales', 5000.00, '2022-02-01'),
    (4, 'Michael Johnson', 'Sales', 4800.00, '2022-03-01'),
    (5, 'David Brown', 'HR', 4000.00, '2022-01-01'),
    (6, 'Jennifer Davis', 'HR', 4200.00, '2022-02-01'),
    (7, 'Davis', 'HR', 4200.00, '2022-02-01'),
    (8, 'Robert Wilson', 'HR', 3800.00, '2022-03-01'),
    (9, 'Emily Thompson', 'Finance', 5500.00, '2022-01-01'),
    (10, 'Daniel Lee', 'Finance', 5200.00, '2022-02-01'),
    (11, 'Olivia Harris', 'Finance', 5300.00, '2022-03-01');

输出

department|employee_name|salary
Finance|Emily Thompson|5500.00
Finance|Olivia Harris|5300.00
HR|Jennifer Davis|4200.00
HR|Davis|4200.00
Sales|John Doe|5000.00
Sales|Jane Smith|5000.00
Sales|Smith|5000.00