sql中with as用法/with-as 性能调优/with用法

深碍√TFBOYSˉ_ 2023-10-13 19:46 18阅读 0赞

#### 文章目录 ####

*  一、概述
 *  二、基本语法
 *  三、使用场景
 *   *  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;

![在这里插入图片描述][fc201f9d25f64cdab8edfe1ca17065be.png]  
②、查询“家电”的平均价格与所有商品的最小最大值

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;

![在这里插入图片描述][f29e92eee9f94d24b96e6b4efc02470e.png]

其实 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 <= 10),
        s2 as (select rownum c2 from dual connect by rownum <= 10)
    select a.c1, b.c2 from s1 a, s2 b where...;

## 四、递归查询 ##

在标准的数据库中，如hive，Oracle，DB2，SQL SERVER，PostgreSQL都是支持 WITH AS 语句进行递归查询。mysql8.0及以上支持递归。

公用表表达式(CTE)具有一个重要的优点，那就是能够引用其自身，从而创建递归CTE。递归CTE是一个重复执行初始CTE以返回数据子集直到获取完整结果集的公用表表达式。

当某个查询引用递归CTE时，它即被称为递归查询。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件)中的数据。

### 4.1、语法 ###

递归cte中包含一个或多个定位点成员，一个或多个递归成员，最后一个定位点成员必须使用"union \[all\]"(mariadb中的递归CTE只支持union \[all\]集合算法)联合第一个递归成员。

更多CTE递归 的其他语法注意事项，请参阅 [递归公用表表达式][Link 1]

with recursive cte_name as (
        select_statement_1       /* 该cte_body称为定位点成员 */
      union [all]
        cte_usage_statement      /* 此处引用cte自身，称为递归成员 */
    )
    outer_definition_statement    /* 对递归CTE的查询，称为递归查询 */

其中：

*  select\_statement\_1：称为"定位点成员"，这是递归cte中最先执行的部分，也是递归成员开始递归时的数据来源。
 *  cte\_usage\_statement：称为"递归成员"，该语句中必须引用cte自身。它是递归cte中真正开始递归的地方，它首先从定位点成员处获取递归数据来源，然后和其他数据集结合开始递归，每递归一次都将递归结果传递给下一个递归动作，不断重复地查询后，当最终查不出数据时才结束递归。
 *  outer\_definition\_statement：是对递归cte的查询，这个查询称为"递归查询"。

### 4.2、使用场景 ###

#### 4.2.1、用with递归构造1-10的数据 ####

# n迭代次数
    with RECURSIVE c(n) as
     (select 1   union all select n + 1 from c where n < 10)
    select n from c;
    
    +------+
    | 	 n |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    |    7 |
    |    8 |
    |    9 |
    |   10 |
    +------+
    10 rows in set (0.00 sec)

#### 4.2.2、with与insert递归造数据 ####

用 WITH 表达式来造数据，非常简单，比如下面例子：给表 y1 添加10条记录，日期字段要随机。

-- 创建测试表
    create table y1 (id serial primary key, r1 int,log_date date);
    
    -- 插入数据
    INSERT y1 (r1,log_date)
       WITH recursive tmp (a, b) AS
       (SELECT
         1,
         '2021-04-20'
       UNION
       ALL
       SELECT
         ROUND(RAND() * 10),
         b - INTERVAL ROUND(RAND() * 1000) DAY
       FROM
         tmp
       LIMIT 10)
    select * from  tmp;

结果：  
![在这里插入图片描述][802e7ff7444640e480b73517d629c64f.png]

#### 4.2.3、with与update更新数据 ####

WITH recursive tmp (a, b, c) AS
        (SELECT
          1,
          1,
          '2021-04-20'
        UNION ALL
        SELECT
          a + 2,
          100,
          DATE_SUB(
            CURRENT_DATE(),
            INTERVAL ROUND(RAND() * 1000, 0) DAY
          )
        FROM
          tmp
        WHERE a < 10)
    UPDATE
         tmp AS a,
         y1 AS b
       SET
         b.r1 = a.b
       WHERE a.a = b.id;

![在这里插入图片描述][877f9db273404abda5b38c690379ea4f.png]

#### 4.2.4、with与delete删除id为奇数的行 ####

> 比如删除 ID 为奇数的行，可以用 WITH DELETE 形式的删除语句：

WITH recursive tmp (a) AS
        (SELECT
          1
        UNION
        ALL
        SELECT
          a + 2
        FROM
          tmp
        WHERE a < 10)
        DELETE FROM y1 WHERE id IN (select * from tmp);

![在这里插入图片描述][1cb3cc25b986447da348fe47f171fcfe.png]  
与 DELETE 一起使用，要注意一点：WITH 表达式本身数据为只读，所以多表 DELETE 中不能包含 WITH 表达式。比如把上面的语句改成多表删除形式会直接报 WITH 表达式不可更新的错误。

WITH recursive tmp (a) AS
         (SELECT
           1
         UNION
         ALL
         SELECT
           a + 2
         FROM
           tmp
         WHERE a < 100)
         delete a,b from y1 a join tmp b where a.id = b.a;
    
    error: [HY000][1288] The target table b of the DELETE is not updatable

#### 4.2.5、with 生成日期序列 ####

> 用 WITH 表达式生成日期序列，类似于 POSTGRESQL 的 generate\_series 表函数，比如，从 ‘2020-01-01’ 开始，生成一个月的日期序列：

WITH recursive seq_date (log_date) AS
          (SELECT
            '2023-07-09'
          UNION
          ALL
          SELECT
            log_date + INTERVAL 1 DAY
          FROM
            seq_date
          WHERE log_date + INTERVAL 1 DAY < '2023-07-20')
          SELECT
            log_date
          FROM
            seq_date;
    
    +-----------+
    |   log_date| 
    +-----------+
    | 2023-07-09|
    | 2023-07-10| 
    | 2023-07-11| 
    | 2023-07-12| 
    | 2023-07-13| 
    | 2023-07-14| 
    | 2023-07-15| 
    | 2023-07-16| 
    | 2023-07-17| 
    | 2023-07-18| 
    | 2023-07-19| 
    +------+

参考文档  
https://docs.oracle.com/cd/E17952\_01/mysql-8.0-en/with.html\#common-table-expressions  
https://dev.mysql.com/doc/refman/8.0/en/with.html\#common-table-expressions-recursive

https://blog.csdn.net/weixin\_43194885/article/details/122199299?utm\_medium=distribute.pc\_relevant.none-task-blog-2defaultbaidujs\_baidulandingword~default-1-122199299-blog-74002447.235v38pc\_relevant\_anti\_t3\_base&spm=1001.2101.3001.4242.2&utm\_relevant\_index=4

https://www.jb51.net/article/236061.htm

[fc201f9d25f64cdab8edfe1ca17065be.png]: https://img-blog.csdnimg.cn/fc201f9d25f64cdab8edfe1ca17065be.png
[f29e92eee9f94d24b96e6b4efc02470e.png]: https://img-blog.csdnimg.cn/f29e92eee9f94d24b96e6b4efc02470e.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
[802e7ff7444640e480b73517d629c64f.png]: https://img-blog.csdnimg.cn/802e7ff7444640e480b73517d629c64f.png
[877f9db273404abda5b38c690379ea4f.png]: https://img-blog.csdnimg.cn/877f9db273404abda5b38c690379ea4f.png
[1cb3cc25b986447da348fe47f171fcfe.png]: https://img-blog.csdnimg.cn/1cb3cc25b986447da348fe47f171fcfe.png