sql - 查询以查找每组的第二高值

Question

编写查询提取部门和每个部门第二高的薪水

员工

| Column         | Value          |
| -------------- | -------------- |
| employee_id    | int            |
| name           | string         |
| department     | string         |
| employment_type| string         |
| salary         | int            |

表格图像

标签： sqlgreatest-n-per-group

Answer 1

我认为这可以帮助你。

SELECT department, MAX(salary) AS second_high FROM Employee
WHERE second_high < (SELECT MAX(salary) FROM Employee)
GROUP BY department