前言

具体的SOL问题

获取每个部门中当前员工薪水最高的员工

代码如下：

//法1：使用开窗函数（经典开窗）
select
    t1.dept_no,t1.emp_no,t1.salary
from
    (
        select
            d.dept_no,d.emp_no,s.salary,rank() over(partition by d.dept_no order by s.salary desc) rn
        from
            dept_emp d
        join
            salaries s
        on
            d.emp_no = s.emp_no
    ) t1
where t1.rn = 1;

//方法2：子查询，经典解法
select
    t1.dept_no,t1.emp_no,t1.salary
from
    (
        select
            d.dept_no,d.emp_no,s.salary
        from
            dept_emp d
        join
            salaries s
        on
            d.emp_no = s.emp_no
    ) t1
where
    (
        select
            count(*) cn
        from
            (
                select
                    d.dept_no,d.emp_no,s.salary
                from
                    dept_emp d
                join
                    salaries s
                on
                    d.emp_no = s.emp_no
            ) t2
          where
                t1.dept_no = t2.dept_no
          and
                t1.salary < t2.salary
    ) = 0
order by t1.dept_no;