查询以查找每个组的第二高值

Question

编写查询以提取每个部门中的部门和第二高工资

员工

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

表格图像

Answer 1

我认为这可以帮助你。

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

Answer 2

使用窗口函数：

select e.department,
max(case when seqnum = 2 then e.salary end) as second_highest
from (select e.*,
dense_rank() over (partition by department order by salary desc) as seqnum
from employee e
) e
group by e.department;

您可以阅读有关dense_rank()功能的文档。在这种情况下；1〃；到工资最高的行；2〃；到具有第二高的行，依此类推。