Oracle子查询实例

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

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

例如：

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

select last_name,salaryfrom s_empwhere salary>940;

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

select salaryfrom s_empwhere last_name='Smith';

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

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

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

分析：

第一步：查询出Smith的工资

select salary from s_emp where last_name='Smith'

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

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

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

分析：

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

select dept_idfrom s_empgroup by dept_idhaving avg(salary)>1247.5

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

select last_name,salary,dept_idfrom s_empwhere dept_id in(select dept_idfrom s_empgroup by dept_idhaving avg(salary)>1247.5);

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

分析：

第一步：查询出Ngao的部门

select dept_idfrom s_empwhere last_name='Ngao'

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

select avg(salary)from s_empwhere dept_id=(select dept_idfrom s_empwhere last_name='Ngao');

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

分析：

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

select dept_idfrom s_empwhere last_name='Ngao'

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

select avg(salary)from s_empwhere dept_id=(select dept_idfrom s_empwhere last_name='Ngao')

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

select dept_idfrom s_empgroup by dept_idhaving avg(salary)>(select avg(salary)from s_empwhere dept_id=(select dept_idfrom s_empwhere last_name='Ngao'))

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

select last_name,salary,dept_idfrom s_empwhere dept_id in(select dept_idfrom s_empgroup by dept_idhaving avg(salary)>(select avg(salary)from s_empwhere dept_id=(select dept_idfrom s_empwhere last_name='Ngao')));

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

分析：

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

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

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

select se.last_name,se.salary,se.dept_id,avgSalaryfrom s_emp se,(select dept_id ,avg(salary) avgSalary from s_emp group by dept_id)tempwhere se.dept_id=temp.dept_idand 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_idfrom s_empgroup by dept_idhaving 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_idfrom s_empwhere dept_id in(select dept_idfrom s_empgroup by dept_idhaving 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');）