欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

update语句中使用子查询

程序员文章站 2022-06-01 17:07:16
...

55. 更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job

1). 搭建骨架

update employees set salary = (

), job_id = (


) where employee_id = 108;

2). 所在部门中的最高工资

select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)

3). 公司中平均工资最低的 job

select job_id
from employees
group by job_id
having avg(salary) =  (
select min(avg(salary))
from employees
group by job_id
)

4). 填充

update employees e set salary = (
select max(salary)
from employees
where department_id = e.department_id
), job_id = (
select job_id
from employees
group by job_id
having avg(salary) =  (
select min(avg(salary))
from employees
group by job_id
)
) where employee_id = 108;

56. 删除 108 号员工所在部门中工资最低的那个员工.
1). 查询 108 员工所在的部门 id

select department_id
from employees 
where employee_id = 108;

2). 查询 1) 部门中的最低工资

select min(salary)
from employees
where department_id = (
select department_id
from employees 
where employee_id = 108;
)

3). 删除 1) 部门中工资为 2) 的员工信息

delete from employees where salary = (
select min(salary)
from employees
where department_id = (
select department_id
from employees 
where employee_id = 108
)
) and department_id = (
select department_id
from employees 
where employee_id = 108
)