SQL之with子句_卖山楂啦prss_sql with

网络 02-07 673

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 ```