Oracle（十五）高级子查询

多列子查询

问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

分析：就是查出来和141、174员工 同一个部门以及同一个领导的其他同事。

不成对比较举例(单列子查询)

select employee_id,manager_id,department_id
from employees e1
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,manager_id,department_id
from employees e1
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 e3 where e1.department_id = e3.department_id)
from employees e1
where salary > (select avg(salary)
                from employees e2
                where e1.department_id = e2.department_id
                group by department_id
                )

方法二

select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select department_id,avg(salary) as avg_sal from employees group by department_id) e2
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal;

三. 单列子查询表达式

case语句

简单case语句

语法:

case exp when comexp then returnvalue
        ...
        when comexp then returnvalue
        else returnvalue
end

case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。
只要把case到end当作一个运算结果的表达式就可以了。

举例:

select cust_last_name,
      case credit_limit when 100 then 'low'
                        when 5000 then 'high'
                        else 'medium'
      end
 from customers;

搜索case语句

语法:

case when boolean then return value
     ...
     when boolean then return value
     else retur nvalue
 end

举例:

select case when id between 1 and 10 then 'low'
        when id between 20 and 30 then 'mid'
        when id between 40 and 50 then 'high'
        else 'unknow'
   end
from product;

简单case和搜索case之间的区别：

• 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。
• searched case可以作为比较条件，那么可以使用like、!=、between ..and、<、=、is null、is not null等，比简单case的使用更加广泛，完全可以替代简单case。

注意事项:

• case 表达式返回的是一个确定的value，若前面的都不匹配，则返回else中的项.
• 简单case 中的表达式，when 后面的表达式类型应该全部保持一致.
• 所有的then 后面的return_value类型要保持一致.
• 对于简单case 表达式，也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null，不会与when null 匹配，只会与else匹配.
• 对于searched case来说，有自动类型转换，只要条件成立就可以。
  如:select case when 1=‘1’ then 1 end from dual; 其中1='1’条件成立

值得一提的是: sql中的case语句与pl/sql中的case语句的不同之处:
前者的else不是必须的，如果没有匹配就返回null;后者的else不写，则报case_not_found异常.

case中嵌套子查询

Case语句中可以使用子查询，但是必须返回一行，不可以是多行.
如：

select case (select count(*) as s1 from t1 where a = 1)
       when (select count(*) as s2 from t1, t2 where t1.a = t2.a and t2.a = 1) then '相等'
       else '不相等'
   end
from dual;

在 CASE 表达式中使用单列子查询

问题：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。

select employee_id,last_name,
(case department_id 
	when (select department_id from departments where location_id = 1800) 
	then 'Canada' 
	else 'USA' 
	end) location
from employees;

在 ORDER BY 子句中使用单列子查询

问题：查询员工的employee_id,last_name,要求按照员工的department_name排序.

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

相关子查询

问题1

查询员工中工资大于本部门平均工资的员工的last_name,
salary和其department_id

select last_name,salary,department_id
from employees outer
where salary > (
                select avg(salary)
                from employees
                where department_id = outer.department_id
                );

问题2

问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

select employee_id,last_name,job_id
from employees e1
where 2 <= (
            select count(*)
            from job_history
            where employee_id = e1.employee_id
            );

EXISTS 操作符

• EXISTS 操作符检查在子查询中是否存在满足条件的行
• 如果在子查询中存在满足条件的行:
• 不在子查询中继续查找
• 条件返回 TRUE
• 如果在子查询中不存在满足条件的行:
• 条件返回 FALSE
• 继续在子查询中查找

EXISTS 操作符应用举例

问题：

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

方法一：

select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
                         select manager_id
                         from employees e2
                         where e1.employee_id = e2.manager_id
                         );

方法二：

select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where e1.employee_id = e2.manager_id;

方法三：

select employee_id,last_name,job_id,department_id
from employees e1
where exists (
              select 'A'
              from employees e2
              where e1.employee_id = e2.manager_id
              );
--解释下上面代码中的'A'，我只要你是管理者，并不需要知道管理者的id

NOT EXISTS 操作符应用举例

问题：查询departments表中，不存在于employees表中的部门的department_id和department_name

select department_id,department_name
from departments d
where not exists (
                  select 'C'
                  from employees
                  where department_id = d.department_id
                  )

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据

相关更新应用举例

1.先创建一个表

create table emp011
as
select *
from employees

2. 增加一列

alter table emp011
add (department_name varchar2(20))

3. 用一个表中的数据更新到另一个表中

update emp011
set department_name = (
                       select department_name
                       from departments
                       where department_id = emp011.department_id
                       )

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据

相关删除应用举例

1. 创建两个表

--表一：    
create table emp022
as
select *
from employees
where department_id in (80,90)
--表二：    
create table emp033
as
select *
from employees
where department_id = 90

2. 删除

delete from emp022
where department_id in (
                       select department_id
                       from emp033
                       where department_id = emp022.department_id
                       )

WITH 子句

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

WITH 子句应用举例

举例1

问题：
查询公司中工资比Abel高的员工的信息

旧方法：

select employee_id,salary
from employees
where salary > (
                select salary
                from employees
                where last_name = 'Abel'
                );

改为with子句

with Abel_sal as (
                  select salary
                  from employees
                  where last_name = 'Abel'
                  )
select employee_id,salary
from employees
where salary > (
                select salary
                from Abel_sal
                );

举例2

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

with dept_sumsal as (
                     select department_name,sum(salary) sum_sal1
                     from departments d, employees e
                     where d.department_id = e.department_id
                     group by department_name
                     ),
dept_avgsal as(
               select sum(sum_sal1)/count(*) avg_sum_sal2
               from dept_sumsal
               )
select *
from dept_sumsal
where sum_sal1 > (
                  select avg_sum_sal2
                  from dept_avgsal
                  )
order by department_name;
--sum_sal1 表示公司中各部门的总工资
--avg_sum_sal2 表示公司中各部门的平均总工资