相关推荐recommended
sql中with as用法with-as 性能调优with用法
作者:mmseoamin日期:2023-12-21

文章目录

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

            sql中with as用法with-as 性能调优with用法,在这里插入图片描述,第1张

            ②、查询“家电”的平均价格与所有商品的最小最大值

            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;
            

            sql中with as用法with-as 性能调优with用法,在这里插入图片描述,第2张

            其实 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递归 的其他语法注意事项,请参阅 递归公用表表达式

            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;
              

              结果:

              sql中with as用法with-as 性能调优with用法,在这里插入图片描述,第3张

              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;
              

              sql中with as用法with-as 性能调优with用法,在这里插入图片描述,第4张

              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);
              

              sql中with as用法with-as 性能调优with用法,在这里插入图片描述,第5张

              与 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