MySQL8.0的WITH查询详情-6ren

MySQL8.0的WITH查询详情

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL8.0的WITH查询详情由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

关于MySQL8的WITH查询学习

前言:

对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性 MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。可以查看官方文档【点击跳转】。

1、示例

官方第一个示例，可以看出该查询语句创建了cte1,cte2,cte3,cte4这4个临时表，后面的临时表依赖前面的临时表数据。最后一行为最终查询结果，实际ct4因为ct3结果包含3行数据，但是使用MAX,MIN得到一行结果.

 
    ? 
   
 
     
       
       
         WITH 
         cte1(txt)  
         AS 
         ( 
         SELECT 
         "This " 
         ), 
        
 
               
         cte2(txt)  
         AS 
         ( 
         SELECT 
         CONCAT(cte1.txt, 
         "is a " 
         )  
         FROM 
         cte1), 
        
 
               
         cte3(txt)  
         AS 
         ( 
         SELECT 
         "nice query" 
         UNION 
        
 
                             
         SELECT 
         "query that rocks" 
         UNION 
        
 
                             
         SELECT 
         "query" 
         ), 
        
 
               
         cte4(txt)  
         AS 
         ( 
         SELECT 
         concat(cte2.txt, cte3.txt)  
         FROM 
         cte2, cte3) 
        
 
         SELECT 
         MAX 
         (txt),  
         MIN 
         (txt)  
         FROM 
         cte4; 
        
 
           
        
 
         + 
         ----------------------------+----------------------+ 
        
 
         |  
         MAX 
         (txt)                   |  
         MIN 
         (txt)             | 
        
 
         + 
         ----------------------------+----------------------+ 
        
 
         | This  
         is 
         a query that rocks | This  
         is 
         a nice query | 
        
 
         + 
         ----------------------------+----------------------+ 
        
 
         1 row  
         in 
         set 
         (0,00 sec) 
        
 
     
 
   

官方第二个示例是递归的用法，根据阅读文档，我分析下面查询结果如下。首先定义一个临时表my_cte 分析SELECT 1 AS n，这个是决定临时表的列名为n，值为1 然后SELECT 1+n FROM my_cte WHERE n<10，这个是递归查询n<10,并将1+n作为结果填充临时表最终使用SELECT * FROM my_cte，查询临时表，因此查询出的结果就显而易见了。

 
    ? 
   
         WITH RECURSIVE my_cte AS 
        
         ( 
        
         SELECT 1 AS n 
        
         UNION ALL 
        
         SELECT 1+n FROM my_cte WHERE n<10 
        
         ) 
        
         SELECT * FROM my_cte; 
        
         +------+ 
        
         | n    | 
        
         +------+ 
        
         |    1 | 
        
         |    2 | 
        
         |    3 | 
        
         |    4 | 
        
         |    5 | 
        
         |    6 | 
        
         |    7 | 
        
         |    8 | 
        
         |    9 | 
        
         |   10 | 
        
         +------+ 
        
         10 rows  
         in 
         set 
         (0,00 sec)

根据我的理解写了如下2个不一样的查询，查询结果都一样。值得注意的是临时表里面的多个查询列数量和类型必须一样，不然会报错.

 
    ? 
   
         这个是将临时表列名指定在第一行 
        
         WITH 
         RECURSIVE my_cte(a,b,c)  
         AS 
        
         ( 
        
         SELECT 
         1,1,1 
        
         UNION 
         ALL 
        
         SELECT 
         1+a,2+b,3+c  
         FROM 
         my_cte  
         WHERE 
         a<10 
        
         ) 
        
         SELECT 
         *  
         FROM 
         my_cte; 
        
         这个第一行没有指定列名，然后列名由第一个查询返回结果确定 
        
         WITH 
         RECURSIVE my_cte  
         AS 
        
         ( 
        
         SELECT 
         1  
         AS 
         a,1  
         AS 
         b,1  
         AS 
         c 
        
         UNION 
         ALL 
        
         SELECT 
         1+a,2+b,3+c  
         FROM 
         my_cte  
         WHERE 
         a<10 
        
         ) 
        
         SELECT 
         *  
         FROM 
         my_cte;

根据官方文档，临时表的语法模板如下，是可以有很多行的查询共同组成.

 
    ? 
   
         WITH 
         RECURSIVE cte_name [list  
         of 
         column 
         names ]  
         AS 
        
         ( 
        
         SELECT 
         ...      < 
         -- specifies initial set 
        
         UNION 
         ALL 
        
         SELECT 
         ...      < 
         -- specifies initial set 
        
         UNION 
         ALL 
        
         ... 
        
         SELECT 
         ...      < 
         -- specifies how to derive new rows 
        
         UNION 
         ALL 
        
         SELECT 
         ...      < 
         -- specifies how to derive new rows 
        
         ... 
        
         ) 
        
         [,  
         any 
         number  
         of 
         other CTE definitions ]

官方文档还列出了，使用临时表时可以增删改查新表，具体可以去阅读官方文档.

3、练习

关于递归的练习主要用于表里面包含父节点id之类的，详情可以参考下面的练习。定义下面这样的表，存储每个区域（省、市、区）的id，名字及上级区域的pid 。

 
    ? 
   
 
     
       
       
         CREATE 
         TABLE 
         tb(id  
         VARCHAR 
         (3), pid  
         VARCHAR 
         (3),  
         name 
         VARCHAR 
         (64)); 
        
 
           
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '002' 
         , 0,  
         '浙江省' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '001' 
         , 0,  
         '广东省' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '003' 
         ,  
         '002' 
         ,  
         '衢州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '004' 
         ,  
         '002' 
         ,  
         '杭州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '005' 
         ,  
         '002' 
         ,  
         '湖州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '006' 
         ,  
         '002' 
         ,  
         '嘉兴市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '007' 
         ,  
         '002' 
         ,  
         '宁波市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '008' 
         ,  
         '002' 
         ,  
         '绍兴市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '009' 
         ,  
         '002' 
         ,  
         '台州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '010' 
         ,  
         '002' 
         ,  
         '温州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '011' 
         ,  
         '002' 
         ,  
         '丽水市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '012' 
         ,  
         '002' 
         ,  
         '金华市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '013' 
         ,  
         '002' 
         ,  
         '舟山市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '014' 
         ,  
         '004' 
         ,  
         '上城区' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '015' 
         ,  
         '004' 
         ,  
         '下城区' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '016' 
         ,  
         '004' 
         ,  
         '拱墅区' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '017' 
         ,  
         '004' 
         ,  
         '余杭区' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '018' 
         ,  
         '011' 
         ,  
         '金东区' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '019' 
         ,  
         '001' 
         ,  
         '广州市' 
         ); 
        
 
         INSERT 
         INTO 
         tb  
         VALUES 
         ( 
         '020' 
         ,  
         '001' 
         ,  
         '深圳市' 
         ); 
        
 
           
        
 
         WITH 
         RECURSIVE cte  
         AS 
         ( 
        
 
           
         SELECT 
         id, 
         name 
         FROM 
         tb  
         WHERE 
         id= 
         '002' 
        
 
           
         UNION 
         ALL 
        
 
           
         SELECT 
         k.id, CONCAT(c. 
         name 
         , 
         '->' 
         ,k. 
         name 
         )  
         AS 
         name 
         FROM 
         tb k  
         INNER 
         JOIN 
         cte c  
         ON 
         c.id = k.pid 
        
 
         )  
         SELECT 
         *  
         FROM 
         cte; 
        
 
     
 
   

执行结果:

MySQL8.0的WITH查询详情

分析结果包含第一行SELECT id,name FROM tb WHERE id='002'的数据，此时表中只有一行数据然后连表查询SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid，递归的将父节点数据放入临时表最终查询出来的就是递归的结果.

到此这篇关于MySQL的WITH查询详情的文章就介绍到这了,更多相关MySQL的WITH查询内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！。

原文链接：https://www.cnblogs.com/janbar/p/15112099.html 。

最后此篇关于MySQL8.0的WITH查询详情的文章就讲到这里了,如果你想了解更多关于MySQL8.0的WITH查询详情的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。