sql中with as用法/with-as 性能调优/with用法
文章目录
- 一、概述
- 二、基本语法
- 三、使用场景
- 3.1、定义CTE,并为每列重命名
- 3.2、多次引用/多次定义
- 3.3、with与union all联合使用
- 3.4、with返回多种结果的值
- 3.5、with与insert使用
- 四、递归查询
- 4.1、语法
- 4.2、使用场景
- 4.2.1、用with递归构造1-10的数据
- 4.2.2、with与insert递归造数据
- 4.2.3、with与update更新数据
- 4.2.4、with与delete删除id为奇数的行
- 4.2.5、with 生成日期序列
一、概述
with as 语句是SQL中的一种常用语法，它可以为一个查询结果或子查询结果创建一个临时表，并且可以在后续的查询中使用这个临时表，在查询结束后该临时表就被清除了。这种语法的使用可以使得复杂的查询变得简单，同时也可以提高查询效率。
WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式（CTE，Common Table Expression）。
with-as 意义：
1、对于多次反复出现的子查询，可以降低扫描表的次数和减少代码重写，优化性能和使编码更加简洁，也可以在UNION ALL的不同部分，作为提供数据的部分。
2、对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
with as语句支持myql、oracle、db2、hive、sql server、MariaDB、PostgreSQL等数据库，以下列举几种数据库支持的版本
- mysql版本：8以及8以上的
- sql server：sql server 2005以后的版本
- oracle：Oracle 9i的第二版本数据库
二、基本语法
with查询语句不是以select开始的，而是以“WITH”关键字开头,可以理解为在进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理。
CTE是使用WITH子句定义的，包括三个部分：CTE名称cte_name、定义CTE的查询语句inner_query_definition和引用CTE的外部查询语句outer_query_definition。
CTE可以在select , insert , update , delete , merge语句的执行范围定义。
它的格式如下：
WITH cte_name1[(column_name_list)] AS (inner_query_definition_1) [,cte_name2[(column_name_list)] AS (inner_query_definition_2)] [,...] outer_query_definition
其中column_name_list指定inner_query_definition中的列列表名，如果不写该选项，则需要保证在inner_query_definition中的列都有名称且唯一，即对列名有两种命名方式：内部命名和外部命名。
注意，outer_quer_definition必须和CTE定义语句同时执行，因为CTE是临时虚拟表，只有立即引用它，它的定义才是有意义的。
示例： -- 单个子查询 with tmp as(select username,userage from user) select username from tmp -- 多个子查询 多个CTE 之间加，分割 with tmp1 as (select * from father), tmp2 as (select * from child) select * from temp1,temp2 on tmp1.id = tmp2.parentId注意：
1.必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
2.with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
3. 如果定义了with子句，但其后没有跟使用CTE的SQL语句（如select、insert、update等），则会报错。
4.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句
5.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。
6.当一个查询块名字和一个表名或其他的对象相同时，解析器从内向外搜索，优先使用子查询块名字。
7.with查询的结果列有别名，引用的时候必须使用别名或*。
三、使用场景
3.1、定义CTE,并为每列重命名
mysql 8.0.34版本中测试以下sql
CREATE TABLE user( id INT NOT NULL PRIMARY KEY, sex CHAR(3),NAME CHAR(20) ); INSERT INTO user VALUES (1,'nan','陈一'), (2,'nv','珠二'), (3,'nv','张三'), (4,'nan','李四'), (5,'nv','王五'), (6,'nan','赵六'); # 定义CTE，顺便为每列重新命名，且使用ORDER BY子句 WITH nv_user(myid,mysex,myname) AS ( SELECT * FROM user WHERE sex='nv' ORDER BY id DESC ) # 使用CTE SELECT * FROM nv_user; +------+-------+-------------+ | myid | mysex | myname | +------+-------+-------------+ | 5 | nv | 王五 | | 3 | nv | 张三 | | 2 | nv | 珠二 | +------+-------+-------------+3.2、多次引用/多次定义
1.多次引用：避免重复书写。
2.多次定义：避免派生表的嵌套问题。
3.可以使用递归CTE，实现递归查询。
# 多次引用，避免重复书写 WITH nv_t(myid,mysex,myname) AS ( SELECT * FROM user WHERE sex='nv' ) SELECT t1.*,t2.* FROM nv_t t1 JOIN nv_t t2 WHERE t1.myid = t2.myid+1; # 多次定义，避免派生表嵌套 WITH nv_t1 AS ( /* 第一个CTE */ SELECT * FROM user WHERE sex='nv' ), nv_t2 AS ( /* 第二个CTE */ SELECT * FROM nv_t1 WHERE id>3 ) SELECT * FROM nv_t2;如果上面的语句不使用CTE而使用派生表的方式，则它等价于：
SELECT * FROM (SELECT * FROM (SELECT * FROM user WHERE sex='nv') AS nv_t1) AS nv_t2;
可以看到这种写法不便于查看。
3.3、with与union all联合使用
前面的with子句定义的查询在后面的with子句中可以使用
with sql1 as (select s_name from test_tempa), sql2 as (select s_name from test_tempb where not exists (select s_name from sql1 where rownum=1)) select * from sql1 union all select * from sql2 union all select ‘no records’ from dual where not exists (select s_name from sql1 where rownum=1) and not exists (select s_name from sql2 where rownum=1);
3.4、with返回多种结果的值
在实际使用中我们可能会遇到需要返回多种结果的值的场景
-- 分类表 CREATE TABLE category ( cid VARCHAR ( 32 ) PRIMARY KEY, cname VARCHAR ( 50 ) ); -- 商品表 CREATE TABLE products ( pid VARCHAR ( 32 ) PRIMARY KEY, pname VARCHAR ( 50 ), price INT, category_id VARCHAR ( 32 ), FOREIGN KEY ( category_id ) REFERENCES category ( cid ) ); -- 分类数据 INSERT INTO category(cid,cname) VALUES('c001','家电'); INSERT INTO category(cid,cname) VALUES('c002','鞋服'); INSERT INTO category(cid,cname) VALUES('c003','化妆品'); INSERT INTO category(cid,cname) VALUES('c004','汽车'); -- 商品数据 INSERT INTO products(pid, pname,price,category_id) VALUES('p001','小米电视机',5000,'c001'); INSERT INTO products(pid, pname,price,category_id) VALUES('p002','格力空调',3000,'c001'); INSERT INTO products(pid, pname,price,category_id) VALUES('p003','美的冰箱',4500,'c001'); INSERT INTO products (pid, pname,price,category_id) VALUES('p004','篮球鞋',800,'c002'); INSERT INTO products (pid, pname,price,category_id) VALUES('p005','运动裤',200,'c002'); INSERT INTO products (pid, pname,price,category_id) VALUES('p006','T恤',300,'c002'); INSERT INTO products (pid, pname,price,category_id) VALUES('p007','冲锋衣',2000,'c002'); INSERT INTO products (pid, pname,price,category_id) VALUES('p008','神仙水',800,'c003'); INSERT INTO products (pid, pname,price,category_id) VALUES('p009','大宝',200,'c003');如上图，如果我想查询“家电”中“格力空调”与“美的冰箱”的信息，不用with as写法如下：
select * from category c left join products p on c.cid = p.category_id where c.cname = '家电' and p.pname in ('格力空调','美的冰箱');使用with as写法如下：
with c as (select * from category where cname = '家电'), p as (select * from products where pname in ('格力空调','美的冰箱')) select * from c,p where c.cid = p.category_id;
②、查询“家电”的平均价格与所有商品的最小最大值
with tem as (select avg(price) as houseElecAvg from products p left join category c on c.cid = p.category_id where c.cname = '家电'), tem1 as (select max(p1.price),min(p1.price) from products p1) select * from tem,tem1;
其实 WITH 表达式除了和 SELECT 一起用， 还可以有下面的组合：
insert with 、with update、with delete、with with、with recursive(可以模拟数字、日期等序列)、WITH 可以定义多张表
3.5、with与insert使用
insert into table2 with s1 as (select rownum c1 from dual connect by rownum
