本文共 3076 字，大约阅读时间需要 10 分钟。

with 语句

with 语句相当于建立了一张 临时虚拟表

即利用with子句为子查询的数据集作为一个内存临时表. 在内存中解析，提高执行效率.，并且提高SQL语句的可读性，用完即销毁。

语法

可以同时定义多个临时表

With 	Subtable1 as (select 1...), //as和select中的括号都不能省略	Subtable2 as (select 2...), //后面的没有with，逗号分割，同一个主查询同级别地方，with子查询只能定义一次	…	Subtablen as (select n...) //与下面的实际查询之间没有逗号Select ….

引用

例子

以下内容引用自

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_brandFROM Users LEFT JOIN  second  //使用子查询ON user_id = seller_id LEFT JOIN Items ON second.item_id = Items.item_id ```