oracle 高级子查询

--查询与141号或174号员工的manager_id和department_id相同的其它员工的employee_id,department_id,manager_id信息
select employee_id,department_id,manager_id from employees 
where manager_id in(select manager_id from employees where employee_id in(141,174))
and department_id in (select department_id from employees where employee_id in(141,174)) 
and employee_id not in(141,174);
--高级子查询,成对比较
select employee_id,department_id,manager_id from employees 
where (manager_id, department_id) in(select manager_id, department_id from employees where employee_id in(141,174))
and employee_id not in(141,174);
--在from子句中使用子查询,返回比本部门平均工资高的员工的last_name,department_id,salary 及平均工资
select last_name,department_id,salary,(select avg(salary) from employees where department_id=e.department_id) from employees e where salary>(
select avg(salary) from employees where department_id= e.department_id  group by department_id  );






select last_name,e.department_id, salary, e2.avg_sal from  employees e,(select department_id, avg(salary) avg_sal from 

employees  group by department_id) e2 where e.department_id=e2.department_id;

--显示员工的employee_id,last_anme,和location

其中若员工department_id和location_id为1800 的department_id 相同，则location为'canada'，否则为'usa';

select last_name,employee_id,
(case department_id when (select department_id from departments  where location_id=1800)
then 'canada'

else 'usa' end ) location  from employees;

--查询员工的last_name,employee_id ,要求按照员工的department_name排序

方法1：

select  employee_id,last_name,department_name from employees e,departments d 
where e.department_id=d.department_id order by department_name;

方法2：相关子查询

select  employee_id,last_name from employees e
order by (select department_name from departments d where e.department_id=d.department_id);

--若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name,job_id;
--方法1：
select employee_id,last_name,job_id from employees where employee_id in(
select employee_id from job_history where employee_id in (select employee_id from employees) having count(employee_id)>1
group by employee_id );
--方法2：逐条查询，符合子查询的，才会输出
select employee_id,last_name,job_id from employees e 
where 2<=(select count(*) from job_history j where j.employee_id=e.employee_id);