sql查询-leetcode184 and 185

链接：力扣https://leetcode.cn/problems/department-highest-salary/力扣https://leetcode.cn/problems/department-top-three-salaries/

184:求工资最高的员工

我的思路：先将两个表进行合并处理，再用max函数在子查询中获取最高工资的数据。

select
    d.name as "Department",
    e.name as "Employee",
    e.salary as "Salary"
from
    Employee as e,Department as d
where
    e.departmentId = d.id
and
    (d.name,e.salary) in
    (select dx.name,max(ex.salary)
     from Employee as ex,Department as dx
     where ex.departmentId = dx.id
     group by dx.name);

题解中大佬的代码：

select department,employee,salary
from(
select b.name as department,a.name as employee,a.salary as salary,
dense_rank() over(partition by departmentId order by salary desc) as 'rank'
from employee a
left join department b on a.departmentId=b.id
) as t
where t.rank=1;

185:求部门前三工资

我的思路：老样子先合成两个表，然后进行二次查询判断比其工资高的人不超过三个，即为前三。

# Write your MySQL query statement below
select
    d.name as "Department",
    e.name as "Employee",
    e.salary as "Salary"
from
    Employee as e,Department as d
where
    e.departmentId = d.id
and
    (select count(distinct ex.salary)
    from Employee as ex
    where ex.departmentId = e.departmentId
    and ex.salary > e.salary)<3;

题解代码：

SELECT 
    department,
    employee,
    salary
FROM 
(
    SELECT 
        b.name AS department,
        a.name AS employee,
        a.salary,
        # 在部门内按照工资降序进行排序
        DENSE_RANK() OVER(PARTITION BY a.departmentid ORDER BY a.salary DESC) AS rk
    FROM employee a LEFT JOIN department b 
    ON a.departmentid = b.id ) c 
    # 筛选出部门工资前三高的员工信息
WHERE rk < 4

总结：

1.184中的子查询可以只用一个表完成。

2.学习DENSE_RANK()函数

关于序号函数-DENSE_RANK()函数

        窗口函数中序号函数的一种（序号函数还有 row_number(),rank()）

序号函数的语法：

window_function () OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
)

window_function：序号函数名

PARTITION BY：根据什么分组，类似group by

ORDER BY：根据什么排序

例如：185题中，题解代码

DENSE_RANK() OVER(PARTITION BY a.departmentid ORDER BY a.salary DESC) AS rk;

desc为降序，asc为升序。

重点解释rk：rk为排序后各行数据的排名。rk<4,即为前三。184题中t.rank=1，即为第一。

关于窗口函数跟多信息，可以参考：

MySQL窗口函教-序号函数（row_number、rank、dense_rank）_mysql 序号函数_奔波霸的伶俐虫的博客-CSDN博客

上述内容也是参考该博客。