with 语句
with 语句相当于建立了一张 临时虚拟表
即利用with子句为子查询的数据集作为一个内存临时表. 在内存中解析,提高执行效率.,并且提高SQL语句的可读性,用完即销毁。
语法
可以同时定义多个临时表
With
Subtable1 as (select 1...), //as和select中的括号都不能省略
Subtable2 as (select 2...), //后面的没有with,逗号分割,同一个主查询同级别地方,with子查询只能定义一次
…
Subtablen as (select n...) //与下面的实际查询之间没有逗号
Select ….
例子
以下内容引用自Oracle with子句的简单介绍.
with
Q1 as (select product_id, product_name from products where rownum <= 10)
select * from Q1 ; //使用Q1
//查询结果
product_id product_name
1797 Inkjet C/8/HQ
2459 LaserPro 1200/8/BW
3127 LaserPro 600/6/BW
2254 HD 10GB /I
3334 HD 12GB /R
Q1为括号的子查询的别名,相当于在内存里建立了1张临时表. 下面的select 语句就直接检索这张临时表
with子句后必须接着select 语句, 否则出错
with
Q1 as (select product_id, product_name from products where rownum <= 10)
delete from products where 1 =2 ;
select * from products where 1 =2;
在第4行上开始执行命令时出错:
with
Q1 as (select product_id, product_name from products where rownum <= 10)
delete from products where 1 =2
命令出错, 行: 17 列: 4
错误报告:
SQL 错误: ORA-00928: 缺失 SELECT 关键字
00928. 00000 - "missing SELECT keyword"
*Cause:
*Action:
未选择任何行
1个with子句里可以定义多个内存临时表, 而且可以互相使用
也就是说
with子句里其中1个内存临时表能被这个with子句内的其他内存临死表使用
WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120
with定义的内存临时表一旦被select 语句检索一次,系统就会在内存中清理掉这张临时表
也就是 用完即销毁
WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
SELECT * FROM Q2;
结果
ORA-00942: 表或视图不存在
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
行 42 列 15 出错
Q2 已经被with里的Q3检索过一次, 被删掉了
with子句定义的临时表命不能于已存在的表名重复
with
products as (select
product_id,
product_name
from products
where rownum <= 10)
select * from products ;
在行 21 上开始执行命令时出错:
with
products as (select product_id, product_name from products where rownum <= 10)
select * from products
命令出错, 行: 22 列: 54
错误报告:
SQL 错误: ORA-32039: 递归 WITH 子句必须具有列别名列表
案例
//构造子查询
WITH r AS (
SELECT seller_id, item_id,
RANK() OVER(PARTITION BY seller_id ORDER BY order_date) AS ranking
FROM Orders),
second AS (
SELECT seller_id, item_id
FROM r
WHERE ranking = 2)
SELECT user_id AS seller_id,
IF(item_brand = favorite_brand, 'yes', 'no') AS 2nd_item_fav_brand
FROM Users
LEFT JOIN second //使用子查询
ON user_id = seller_id
LEFT JOIN Items
ON second.item_id = Items.item_id ```