MySQL高阶1965-丢失信息的雇员
目录
题目
准备数据
分析数据
实现
题目
编写解决方案，找到所有 丢失信息 的雇员 id。当满足下面一个条件时，就被认为是雇员的信息丢失：
- 雇员的 姓名 丢失了，或者
- 雇员的 薪水信息 丢失了
返回这些雇员的 id employee_id ， 从小到大排序 。
准备数据
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 Employeesinsert 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 Salariesinsert 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')Salaries表
Employees表
分析数据
第一步:通过union all对两个select语句进行连接,并通过左连接和右连接将不符合的数据为null
select e.Employee_id,e.name,s.salary from Employees e left join Salaries s on e.employee_id=s.employee_id union all select s1.employee_id,e1.name,s1.salary from Employees e1 right join Salaries s1 on e1.employee_id=s1.employee_id;
第二步:选出符合条件的,并且进行排序
with a as(select e.Employee_id,e.name,s.salaryfrom Employees e left join Salaries s on e.employee_id=s.employee_idunion allselect s1.employee_id,e1.name,s1.salaryfrom Employees e1 right join Salaries s1 on e1.employee_id=s1.employee_id) select employee_id from a where name is null or salary is null order by employee_id;
实现
with a as(select e.Employee_id,e.name,s.salaryfrom Employees e left join Salaries s on e.employee_id=s.employee_idunion allselect s1.employee_id,e1.name,s1.salaryfrom Employees e1 right join Salaries s1 on e1.employee_id=s1.employee_id)
select employee_id
from a
where name is null or salary is null
order by employee_id;