MySQL in和exists到底怎么用最合适

903

5页

2次

2020-08-11

5墨值下载

leetcode 一个题目及思考：in 和 exists 到底什么时候用最合适？

题目如下：

编写一个 SQL 查询，找出每个部门工资最高的员工。

（同样的解法 MySQL 8.0 之后也可以方便的使用开窗函数去解，可以参考前几篇文章）

我们本次使用 in 的方式：

我们来看下 in 的几种等效方式

Employee 表包含所有员工信息

Department 表包含公司所有部门的信息

预期结果

解决思路：每个部门的最高工资，都能想到按照部门分组，取 max（salary），

关键是需要去到名字，名字存在 employee 中，group by 的字段加不上去，比较麻烦。

我们可能会想到子查询，突然想起看到有同学写的 sql：

select * from table A where id =（select xx_id from table B where name='XXX'）

and dep_id=（select dep_id from table B where name='XXX'）

我只想问：

select * from table A where (id,dep_id) in (select * from table_B where name='XXX')

他不香吗？但是可能会有很多同学用的多字段 in 比较少，不容易想起。

废话少说我们来看下这题：直接说 leetcode 的写法。

SELECT

e.NAME AS Employee,

e.Salary,

d.NAME AS Department

FROM

department d

LEFT JOIN employee e ON d.id = e.dep_id

WHERE

-- leetcode 解法，多个字段 in 这个可能平时用的少，不容易想到

-- (e.dep_id,e.salary) in (SELECT dep_id, MAX( salary ) AS salary FROM employee

GROUP BY dep_id )

EXISTS (

SELECT

FROM

( SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP

BY dep_id ) AS t

WHERE

e.dep_id = t.dep_id

AND e.salary = t.salary

)

还有其他改写方式么？来看看

MySQL 8.0 也支持代码块了，改写后更美观了。可读性更强了

WITH cte AS ( SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP BY

dep_id ) SELECT

e.NAME AS Employee,

e.Salary,

d.NAME AS Department

FROM

department d

LEFT JOIN employee e ON d.id = e.dep_id

WHERE

EXISTS ( SELECT 1 FROM cte AS t WHERE e.dep_id = t.dep_id AND e.salary

= t.salary )

还可以怎么改？MySQL 8.0 同样由 ANY 写法

SELECT

e.NAME AS Employee,

e.Salary,

d.NAME AS Department

FROM

department d

LEFT JOIN employee e ON d.id = e.dep_id

of 5

5墨值下载

mysql

关注

评论