Oracle子查询实例

这里用到的表是Oracle官方提供的 s_emp表

之前我们所用到的查询都是简单的查询，条件语句都是表中的某个字段

例如：

1.查询比940工资高的员工的xinxi

select last_name,salary
from s_emp
where salary>940;

2.查询出叫Smith的员工的信息

select salary
from s_emp
where last_name='Smith';

而实际应用中，常常会用到 子查询，而子查询是 将一个查询语句作为本次查询的条件的一部分

以下就是一些有关子查询的例子

1.查询工资比Smith工资高的员工信息

分析：

第一步：查询出Smith的工资

select salary from s_emp where last_name='Smith'

第二步：查询出比（ 第一步结果）更大的员工信息

select last_name,salary
from s_emp
where salary>(select salary from s_emp where last_name='Smith');

2.查询平均工资比1247.5高的部门中员工信息

分析：

第一步：查询出平均工资比1247.5高的部门ID

select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5

第二步：查询出这些部门（ 第一步结果）的员工信息

select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
);

3.查询Ngao所在部门的平均工资

分析：

第一步：查询出Ngao的部门

select dept_id
from s_emp
where last_name='Ngao'

第二步：查询出部门（ 第一步结果）的平均工资

select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
);

4.查询平均工资比Ngao所在部门平均工资高的部门中员工信息

分析：

第一步：查询出Ngao所在部门

select dept_id
from s_emp
where last_name='Ngao'

第二步：查询出部门（ 第一步结果）的平均工资

select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
)

第三步：查询出平均工资比（ 第二步结果）大的部门

select dept_id
from s_emp
group by dept_id
having avg(salary)>(select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
))

第四步：查询出部门（ 第三步结果）的员工信息

select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
))
);

5.查询员工工资比自己所在部门的平均工资高的员工的信息

分析：

第一步：查询自己所在部门的员工的平均工资和基本信息

select se.last_name,se.salary,se.dept_id,avgSalary
from s_emp se,(select dept_id ,avg(salary) avgSalary from s_emp group by dept_id
)temp
where se.dept_id=temp.dept_id;

第二步：查询员工工资比（ 第一步结果）高的员工的信息

select se.last_name,se.salary,se.dept_id,avgSalary
from s_emp se,(select dept_id ,avg(salary) avgSalary from s_emp group by dept_id
)temp
where se.dept_id=temp.dept_id
and se.salary>temp.avgSalary;

6.查询平均工资比Ngao所在部门平均工资高的部门中员工信息,并且这些员工所在部门的平均工资也要比Ngao所在部门平均工资高。

分析:

第一步：查询出Ngao所在部门的平均工资：

select avg(salary) from s_emp  
where dept_id=(select dept_id from s_emp where last_name='Ngao');	  

第二步：查询出哪些部门的平均工资比Ngao所在部门平均工资高

select dept_id
from s_emp
group by dept_id
having avg(salary)>(select avg(salary) from s_emp  where dept_id=(select dept_id from s_emp where last_name='Ngao') group by dept_id );

第三步：整合前两步的结果，最终还是查找员工信息，只是这些员工要满足两部分条件

select last_name,salary,dept_id
from s_emp
where dept_id in(select dept_id
from s_emp
group by dept_id
having avg(salary)>(select avg(salary) from s_emp  where dept_id=(select dept_id from s_emp where last_name='Ngao') group by dept_id ))
and salary>（select avg(salary) 
from s_emp  
where dept_id=(select dept_id from s_emp where last_name='Ngao');）