以例子学习with:
with
--查询部门和部门的总薪水
dept_costs as (
select d.department_name,sum(e.salary) dept_total
from departments d,employees e
where d.department_id=e.department_id
group by d.department_name
),
--利用上一个with查询的结果,计算部门的平均总薪水
avg_costs as (
select avg(dept_total) dept_avg
from dept_costs
)
--从两个with查询中比较并且输出查询结果
select *
from dept_costs
where dept_total > (select dept_avg from avg_costs)
order by department_name
注释:
① 子查询可重用相同或者前一个with查询块,通过select调用(with子句也只能被select调用)
② with子句的查询输出存储到用户临时表空间,一次查询,到处使用
③ 同级select前有多个查询定义,第一个用with,后面的不用with,并且用逗号分割
④ 最后一个with查询块与下面的select调用之间不能用逗号分割,只通过右括号分离,with子句的查询必须括号括起
⑤ 如果定义了with子句,而在查询中不使用,则会报ora-32035错误,只要后面有引用的即可,不一定在select调用,在后with查询块引用也是可以的
⑥ 前面的with子句定义的查询在后面的with子句中可以使用,但是一个with子句内部不能嵌套with子句
⑦ with查询的结果列有别名,引用时候必须使用别名或者*
再来看with的语法
㈠ as和select中的括号不能省略
㈡ 同级别select调用,with只能定义一次,多个用逗号分隔,但最后一个with子查询与下面的实际查询之间没有逗号
with子句的优点
① with子句有可能会改变执行计划
② with子查询只执行一次,将结果存储在用户的临时表空间,可多次引用,增加性能
③ sql的可读性较强
案例:
Ⅰ一般使用方式
with
--查询销售部门员工的姓名
saler_name as (
select department_id from departments where department_name='SALES' order by department_id
)
select last_name,first_name
from employees e
where department_id in (select * from saler_name)
注释:使用with子句,可以在复杂的查询中预先定义好一个结果集,然后在查询中反复使用,不使用会报错。而且with子句获得的是一个临时表,必须采用select from (with查询名)
Ⅱ 在多数子查询中引用,同级可见
select last_name
from (with
--查询销售部门员工的姓名
saler_name as (
select department_id from departments where department_name='SALES' order by department_id
)
select last_name,first_name
from employees e
where department_id in (select * from saler_name)
)
Ⅲ 在集合在引用
集操作的两个select调用被当做是同级的,不能出现两个with定义
with
--查询销售部门员工的姓名
saler_name as (
select department_id from departments where department_name='SALES' order by department_id
)
select last_name,first_name
from employees e
where department_id in (select * from saler_name)
union all
select last_name,to_char(null)
from employees
分享到:
相关推荐
Oracle start with.connect by prior子句实现递归查询
下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法,具体内容如下所示: SQL> with emp_data(ename,empno,mgr,l) as (select ename, empno, mgr, 1 lvl from emp where mgr is null union all select emp....
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
oracle,select查询,单行函数,多表查询数据,组函数,子查询,高级子查询以及_exist_with子句,SQLPLUS。
Oracle SELECT语句中的START WITH和CONNECT BY...START WITH子句指定被认为是层次起点,或“根”的一行或几行,然后CONNECT BY PRIOR子句指明哪些行彼此关联。本文将为大家介绍如何在Oracle层次查询中给SIBLINGS排序。
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
在Oracle中用Start with...Connect By子句递归查询
with子句使用,集合操作,case应用,行列互换,4 exists与in、not exists与not in
通过本章学习,您将可以: 书写多列子查询 子查询对空值的处理 在 FROM 子句中使用子查询 在SQL中使用单列子查询 相关子查询 书写相关子查询 使用子查询更新和删除数据 使用 EXISTS 和 NOT EXISTS 操作...使用 WITH 子句
ORA-01402:视图WITH CHECK OPTION 违反WHERE 子句 视图的删除:DROP VIEW VIEW_NAME语句删除视图。 删除视图的定义不影响基表中的数据。 只有视图所有者和具备DROP VIEW权限的用户可以删除视图。 视图被删除...
9 储存结构与关系 目标 9-2 概述 9-3 段的类型 9-4 储存子句的优先次序 9-7 片的分配和重新分配 9-8 使用和释放片 9-9 数据块 9-10 数据块的内容 9-11 块空间的利用的参数 9-12 v 自动段空间管理 9-14 设置自动...
相关子查询在对每个候选行必须返回一个不同结果的子查询是有用的 EXISTS运算符是一个布尔林运算符,测试数值是否存在 ...当相同的查询发生多次,你可以使用WITH子句在SELECT命令中使用相同的查询块
原书名: OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052, 1Z0-053 原出版社: McGraw-Hill Osborne Media 作者: (美)John Watson Roopesh Ramklass Bob Bryla 译者: ...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...
如果视图定义包括条件(譬如 WHERE 子句)并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句,则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的...
5.Where子句可以接收From子句输出的数据,而HAVING子句则可以接收来自WHERE、FROM或GROUP BY子句的输入。 6.在SQL语句中,用于向表中插入数据的语句是Insert。 7.如果需要向表中插入一批已经存在的数据,可以在...
实验29:树结构的查询start with子句 66 实验30:高级dml操作 68 第二部分pl/sql基础 69 匿名块的编写 69 实验31:书写一个最简单的块,运行并查看结果 69 实验32:在块中操作变量 70 实验33:在块中操作表的数据 71...
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
原书名: OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052, 1Z0-053 原出版社: McGraw-Hill Osborne Media 作者: (美)John Watson Roopesh Ramklass Bob Bryla 译者: ...