子查询

-- 子查询
-- 先根据"汤姆"查询出对应的部门编号！
select dept_id from tb_emp where emp_name = '汤姆';
-- 在部门信息表，根据部门编号查询部门名称！
select dept_name from tb_dept where dept_id = 101;
-- 组合
select dept_name from tb_dept where dept_id = (
        select dept_id from tb_emp where emp_name = '汤姆'
);
-- 查询“cat”在10月份工资情况！
-- 先根据"cat"查询对应的员工编号
select emp_id from tb_emp where emp_name = 'cat';
-- 根据员工编号2查询工资情况
select * from tb_salary where s_put_time = '2022-10-10' and emp_id = 2;
-- 组合
select * from tb_salary where s_put_time = '2022-10-10' and emp_id = (
        select emp_id from tb_emp where emp_name = 'cat'
);
-- 查询“汤姆”和“cat”相同部门的员工信息！
-- 根据“汤姆”和“cat”查询对应的部门编号
select dept_id from tb_emp where emp_name = '汤姆' or emp_name ='cat';
-- 根据部门编号查找员工信息
select * from tb_emp where dept_id = 101;
-- 当子查询返回是多个结果集！ 使用in
select * from tb_emp where dept_id in (
        select dept_id from tb_emp where emp_name = '汤姆' or emp_name ='cat'
);

-- 子查询当做临时表来使用！
select * from tb_emp e, tb_salary s where e.emp_id = s.s_id order by dept_id;
select * from (
        select e.*,s.s_put_time,s.s_salary,s.s_state from tb_emp e, tb_salary s where e.emp_id = s.s_id
)tab where tab.s_put_time > '2022-10-01';
-- 查看每个部门最高工资的员工信息
-- 根据部门来分组，获取最高工资
select e.dept_id,max(s.s_salary) from tb_emp e, tb_salary s where e.emp_id = s.emp_id
group by e.dept_id;
-- 根据部门编号和工资查询员工信息
select e.* from tb_emp e, tb_salary s where e.emp_id = s.emp_id
and e.dept_id = 101 and s.s_salary = 60000;
-- 合并
select e.* from tb_emp e, tb_salary s,(
        select e.dept_id,max(s.s_salary) maxsal from tb_emp e, tb_salary s where e.emp_id = s.emp_id
        group by e.dept_id
)tab where e.emp_id = s.emp_id and e.dept_id = tab.dept_id and s.s_salary = tab.maxsal;
-- 查看每个部门最高工资的员工信息 --> any
select e.* from tb_emp e, tb_salary s where e.emp_id = s.emp_id
and (e.dept_id,s.s_salary) = any (
        select e.dept_id,max(s.s_salary) from tb_emp e, tb_salary s where e.emp_id = s.emp_id
        group by e.dept_id
);

-- 临时列使用
select e.*,(
        select dept_name from tb_dept where dept_id = e.dept_id) deptName
from tb_emp e;