- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
Employees
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id 是该表中具有唯一值的列。 每一行表示雇员的 id 和他的姓名。表:
Salaries
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | salary | int | +-------------+---------+ employee_id 是该表中具有唯一值的列。 每一行表示雇员的 id 和他的薪水。编写解决方案,找到所有 丢失信息 的雇员 id。当满足下面一个条件时,就被认为是雇员的信息丢失:
- 雇员的 姓名 丢失了,或者
- 雇员的 薪水信息 丢失了
返回这些雇员的 id
employee_id
, 从小到大排序 。查询结果格式如下面的例子所示。
示例 1:
输入: Employees table: +-------------+----------+ | employee_id | name | +-------------+----------+ | 2 | Crew | | 4 | Haven | | 5 | Kristian | +-------------+----------+ Salaries table: +-------------+--------+ | employee_id | salary | +-------------+--------+ | 5 | 76071 | | 1 | 22517 | | 4 | 63539 | +-------------+--------+ 输出: +-------------+ | employee_id | +-------------+ | 1 | | 2 | +-------------+ 解释: 雇员 1,2,4,5 都在这个公司工作。 1 号雇员的姓名丢失了。 2 号雇员的薪水信息丢失了。
三,建表语句
Create table If Not Exists Employees (employee_id int, name varchar(30));
Create table If Not Exists Salaries (employee_id int, salary int);
Truncate table Employees;
insert into Employees (employee_id, name) values ('2', 'Crew');
insert into Employees (employee_id, name) values ('4', 'Haven');
insert into Employees (employee_id, name) values ('5', 'Kristian');
Truncate table Salaries;
insert into Salaries (employee_id, salary) values ('5', '76071');
insert into Salaries (employee_id, salary) values ('1', '22517');
insert into Salaries (employee_id, salary) values ('4', '63539');
select * from Employees;
select * from salaries;
四,分析
解题思路:
第一步: 左连接 取3个字段
第二步 右连接 取对应相关的列
第三步,全连接 使用union 去重
最后一步,筛选包含null值的 id 并且排序
五,SQL解答
with t1 as (
select e1.employee_id as employee_id,
e1.name as name,
s1.salary as salary
from employees e1 left join salaries s1 on e1.employee_id=s1.employee_id
),t2 as (
select s1.employee_id as employee_id,
e1.name as name,
s1.salary as salary
from employees e1 right join salaries s1 on e1.employee_id=s1.employee_id
),t3 as (
select * from t1
union
select * from t2
),t4 as (
select employee_id from t3 where name is null or salary is null order by employee_id)
select * from t4;
六,验证
七,知识点总结
- 左连接的运用
- 右连接的运用
- 在mysql并不支持全连接 所以用左连接+右连接
- is null的运用
- 排序的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用