orcle之高级子查询

子查询：

子查询是嵌套在 SQL 语句中的另一个SELECT 语句

代码示例：

--找出工资大于平均工资的所有人
select employee_id,last_name,salary
from employees
where salary>
(select avg(salary) from employees)

多列子查询：

主查询与子查询返回的多个列进行比较

成对比较：

代码示例：

--查询与141号或174号员工的manager_id和
--department_id相同的其他员工的employee_id, manager_id, department_id  
select * from employees 
where (manager_id,department_id)in(
select manager_id,department_id from employees 
where employee_id in (141,147)
) and employee_id not in (141,147)

不成对比较：

代码示例：

select * from employees where manager_id in (
select manager_id from employees
where employee_id in (141,147)
)
and department_id in (
select department_id from employees where employee_id in (141,147)
)

 

在 FROM 子句中使用子查询：

代码示例：

--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
--用表连接
select last_name,e.department_id,salary,m.avg_s
from employees e,
(select avg(salary) avg_s,department_id from employees group by department_id)m 
where e.department_id=m.department_id
and e.salary>m.avg_s

--用子查询
select employee_id ,salary,f.avg_sal from employees e ,
(select avg(salary) avg_sal,department_id from employees 
group by department_id) f
where e.department_id=f.department_id
and salary>(
select avg(salary) from employees d 
where e.department_id=d.department_id)

 

相关子查询：

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询 

 SELECT column1, column2, ...  

FROM   table1  outer

WHERE  column1 operator               

(SELECT  colum1, column2                        

FROM    table2                        

WHERE   expr1 = outer.expr2);

子查询中使用主查询中的列

代码示例：

---找换过两次工作的人
select employee_id,first_name from employees e where
2<=
(select count(*) from job_history where 
employee_id=e.employee_id)

-- 工资大于本部门平均工资的人
select last_name,salary, department_id
from employees outer
where salary >(
             select avg(salary) 
             from employees
             where department_id=outer.department_id)

EXISTS 操作符:

EXISTS 操作符检查在子查询中是否存在满足条件的行

如果在子查询中存在满足条件的行: 不在子查询中继续查找 

                                                       条件返回 TRUE

如果在子查询中不存在满足条件的行: 条件返回 FALSE   

                                                         继续在子查询中查找

举列：

问题：查询公司管理者的employee_id,last_name,job_id,department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

WITH 子句：

• 使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
• WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
• 使用 WITH 子句可以提高查询效率

举列：

问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

WITH 
dept_costs  AS (
   SELECT  d.department_name, SUM(e.salary) AS dept_total
   FROM    employees e, departments d
   WHERE   e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost    AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM   dept_costs)
SELECT * 
FROM   dept_costs 
WHERE  dept_total >
        (SELECT dept_avg 
         FROM avg_cost)
ORDER BY department_name;