编写查询以提取部门和每个部门中第二高的薪水
员工
| Column | Value |
| -------------- | -------------- |
| employee_id | int |
| name | string |
| department | string |
| employment_type| string |
| salary | int |
我认为这可以帮助您。
SELECT department,MAX(salary) AS second_high FROM Employee
WHERE second_high < (SELECT MAX(salary) FROM Employee)
GROUP BY department
使用窗口函数：
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”分配给第二高的行，依此类推。