• Oracle子查询之高级子查询


    Oracle 高级子查询

      高级子查询相对于简单子查询来说,返回的数据行不再是一列,而是多列数据。

    1,多列子查询

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

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

    select employee_id, manager_id, department_id

    from employees

    where (manager_id,department_id) in ( --由于内查询返回两列,所以此处也必须使用两列来进行对应,并且列的顺序一致

    select manager_id,department_id --内查询返回2列

    from employees

    where employee_id in (141,174)

    )

    and employee_id not in (141,174);

    2,FROM子句中使用子查询

    将子查询的结果集,作为一个虚表,然后从这个虚表中查询数据行

    返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

    select last_name, e.department_id, salary,avgsal

    from employees e,(select department_id,round(avg(salary)) avgsal

    from employees

    group by department_id) avg_sal

    where e.department_id = avg_sal.department_id

    and e.salary > avg_sal.avgsal

    order by e.department_id;

    3,单列子查询的其他应用

    在主查询的select列表中使用

    问题:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,

    则location为’Canada’,其余则为’USA’。

    select employee_id,last_name,department_id,

    (case when department_id = (select department_id

    from departments

    where location_id = 1800

    )

    then 'USA'

    else 'Canada' end) location

    from employees

    order by department_id;

    select employee_id,last_name,department_id,

    (case department_id when (select department_id

    from departments

    where location_id = 1800

    )

    then 'USA'

    else 'Canada' end) location

    from employees

    order by department_id;

    select employee_id,last_name,department_id,

    decode(department_id,(select department_id

    from departments

    where location_id = 1800

    ),'USA',

    'Canada') location

    from employees

    order by department_id;

    4,相关子查询,

    子查询语句中使用了主查询语句中的表中的数据(这个数据不一定在主查询的select语句中)

    相关子查询按照一行接一行的顺序执行,即子查询取主查询表中的每一行数据值,主查询的每一行都执行一次子查询。

    使用相关子查询,要考虑是否必须使用、是否合理,否则在不需要使用相关子查询的情况下就能轻易得到查询结果,使用相关子查询反而会使查询的效率下降。

    用法:

    get:从主查询所用的表中获取候选列

    execute:子查询使用主查询的数据,并进行相关的筛选

    use:如果满足内查询的条件则返回值

    例1:查询员工的employee_id,last_name,要求按照员工的department_name排序

    select employee_id,last_name,e.department_id,department_name

    from employees e,departments dd

    where e.department_id = dd.department_id(+) --该查询返回employees表中的所有行,没返回一行,都会取一个employee_id到子查询中

    order by (select department_name

    from departments d

    where e.department_id = d.department_id); --子查询根据主查询返回的employee_id,在departments表中进行查找,有则返回数据。

    desc;

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

    select last_name,salary,e1.department_id,ss.avgsal

    from employees e1,(select department_id,round(avg(salary)) avgsal

    from employees

    group by department_id) ss --该from字句同前例

    where e1.department_id = ss.department_id

    and salary > (

    select round(avg(salary))

    from employees e2

    where e1.department_id = e2.department_id --将外查询中的department_id,查找该处的department_id,并将所有的返回结果分组

    group by e2.department_id)

    order by e1.department_id;

    例3:若employees表中employee_id与job_history表中employee_id相同且job_history表中employee_id的数目不小于2,

    输出employees中这些相同id的员工的employee_id,last_name和其job_id

    select e1.employee_id ,last_name,e1.job_id

    from employees e1

    where (select count(job_id)

    from job_history j1

    where e1.employee_id = j1.employee_id) >= 2

    5,exists

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

    如果在子查询中存在满足条件的行:

    不在子查询中继续查找

    条件返回 TRUE

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

    条件返回 FALSE

    继续在子查询中查找

    查询公司管理者的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 distinct(manager_id)

    from employees);

    自连接

    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 e1.employee_id in (

    select manager_id

    from employees e2

    where e1.employee_id = e2.manager_id);

    使用exists相关子查询

    select employee_id,last_name,job_id,department_id

    from employees e1

    where exists (

    select 'A' --根据e1表中的每个 employee_id在e2中查找 manager_id,如果找到,子查询返回true,主查询返回该数据行

    from employees e2

    where e1.employee_id = e2.manager_id);

    6.not exists

    查询departments表中,不存在于employees表中的部门的department_id和department_name

    select d.department_id,d.department_name

    from departments d

    where not exists ( --not exists 返回子查询中false的结果

    select 'X' --根据e1表中的每个 employee_id在e2中查找 manager_id,如果找到,子查询返回true,即该部门在departments中存在,也有员工

    from employees e

    where d.department_id = e.department_id);

    7.相关更新

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

    例:向employees中添加一列 department_name ,并更具department_id填充

    update employees e1

    set department_name = (

    select department_name

    from departments

    where e1.department_id = department_id);

    8,相关删除

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

    例:删除表employees中,其与emp_history表皆有的数据

    delete from employees e1

    where employee_id in (

    select employee_id

    from job_history

    where e1.employee_id = employee_id);

    9,使用 WITH 子句

    可以避免在 SELECT 语句中重复书写相同的语句块

    WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

    使用 WITH 子句可以提高查询效率

    例,查询公司中各部门的总工资,大于公司中各部门的平均总工资,的部门信息

    使用普通方法:使用普通方法,汇总后的语句冗长不易理解

    --各部门总工资

    select department_id,sum(salary) sum_sal from employees group by department_id;

    --各部门的平均总工资

    select sum(to_sal.sum_sal)/count(*) to_avg_sal

    from (select department_id,sum(salary) sum_sal

       from employees group by department_id) to_sal;

    --根据前面两项,查询最终结果 

    select to_sal.department_id,sum_sal

    from (select department_id,sum(salary) sum_sal from employees group by department_id) to_sal,

       (select sum(to_sal.sum_sal)/count(*) to_avg_sal from (select department_id,sum(salary) sum_sal from employees group by department_id) to_sal) avg_sal

    where to_sal.sum_sal > avg_sal.to_avg_sal;

    使用with字句

    with to_sal as --各部门总工资

    (select department_id,sum(salary) sum_sal from employees group by department_id),

    avg_sal as --各部门的平均总工资

    (select sum(to_sal.sum_sal)/count(*) to_avg_sal from to_sal)

    select department_id,sum_sal

    from to_sal

    where to_sal.sum_sal > (

    select avg_sal.to_avg_sal

    from avg_sal);

    with

    to_sal as

    (select department_id,sum(salary) sum_sal from employees group by department_id),

    avg_sal as

    (select sum(to_sal.sum_sal)/count(*) to_avg_sal from to_sal)

    select department_id,sum_sal,to_avg_sal

    from to_sal,avg_sal

    where to_sal.sum_sal > avg_sal.to_avg_sal;

     注明:本博文系学习尚硅谷网易云课堂教学课程总结输出。

  • 相关阅读:
    XML和JSON的区别
    浏览器渲染和服务器渲染区别
    存储过程
    对象关系映射(ORM)
    GitHub和SVN的区别
    WEB网站常见受攻击方式及解决办法
    同源与跨域
    java常用API之DateFormat
    java常用API之Date类
    面型对象之匿名对象
  • 原文地址:https://www.cnblogs.com/Clonglegs/p/9479955.html
Copyright © 2020-2023  润新知