with 语句

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

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

语法

可以同时定义多个临时表

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

引用高级sql学习——with子句！！！

例子

以下内容引用自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 ```