mysql with 的用法 (含 with recursive)

最新推荐文章于 2024-05-10 08:00:00 发布
最新推荐文章于 2024-05-10 08:00:00 发布
阅读量1k 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zzsan/article/details/134337090
版权

mysql with 的用法 (含 with recursive)

相关基础

AS 用法

as 在 mysql 中用来给列/表起别名
如:

-- 给列起别名, 把列为name的别名命名为student_name
select name as student_name from student;
-- 给表起别名, 把表student的别名命名为data_list
select * from student as data_list;
-- 给查询结果/表达式起别名
select length(name) as name_length from student;

UNION 用法

union 用于结合多个 sql 查询结果于单个结果集中

query_expression_body UNION [ALL | DISTINCT] query_block
    [UNION [ALL | DISTINCT] query_expression_body]
    [...]

mysql> SELECT 1, 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+

mysql> SELECT 'a', 'b';
+---+---+
| a | b |
+---+---+
| a | b |
+---+---+

mysql> SELECT 1, 2 UNION SELECT 'a', 'b';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| a | b |
+---+---+

with (Common Table Expressions)

Common Table Expressions（CTE）是一个命名的临时结果集，存在于单个语句的范围内，以后该临时结果集可以在该语句中引用, 甚至可能多次引用。
语法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

示例:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

--下面两个查询等价
WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

用法

  1. 在 select, update, delete 语句前

    WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

  2. 在子查询前

    SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

  3. 于含 select 的语句的 select 前

    INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

  4. 同一级别只允许一个 WITH 子句

    -- 错误示范
WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
-- 正确示范1
WITH cte1 AS (...), cte2 AS (...) SELECT ...
-- 正确示范2, 语句中可含有多个with, 前提是他们都在不同的层级
WITH cte1 AS (SELECT 1)
    SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

  5. 一个 with 语句能定义一个或多个 CTE, 但每个 CTE 在该语句中都是唯一的

    -- 错误示范, 两个cte命名都是cte1
WITH cte1 AS (...), cte1 AS (...) SELECT ...
-- 正确示范
WITH cte1 AS (...), cte2 AS (...) SELECT ...

递归 CTE (Recursive Common Table Expressions)

示例 1 递归增长

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

上述 sql 输出结果如下:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

该 sql 可以分成两部分, 一部分是非递归部分, 用于初始化行数据:

SELECT 1

另一部分是递归部分:

SELECT n + 1 FROM cte WHERE n < 5

等价于以下代码:

(function test(a) {
  console.log(a);
  a++;
  if (a <= 5) {
    test(a);
  }
})(1);

// 1
// 2
// 3
// 4
// 5

示例 2 递归字符串拼接

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

在非严格模式下, 输出以下内容:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

严格模式下, 则会报错: ERROR 1406 (22001): Data too long for column 'str' at row 1

定义str列时, 用abc定义, 该操作同时定义了长度为length(abc), 故拼接时, 会超出长度
将上述 sql 调整一下

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

即可正常输出:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

限制 CTE 循环次数

输入以下 sql 时, 会提示Recursive query aborted after 1048577 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下, cte_max_recursion_depth的值为 1000, 会限制 CTE 的循环次数, 可以通过修改cte_max_recursion_depth修改循环次数上限.

通过修改cte_max_recursion_depth修改循环次数上限后, 可通过limit限制上限.

cte_max_recursion_depth>=limit
故当 limit 较大时, 需先修改cte_max_recursion_depth, 否则较大的limit不生效

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

斐波那契数列

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

日期序列生成

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2022-01-03 | 100.00 |
| 2022-01-03 | 200.00 |
| 2022-01-06 |  50.00 |
| 2022-01-08 |  10.00 |
| 2022-01-08 |  20.00 |
| 2022-01-08 | 150.00 |
| 2022-01-17 |   5.00 |
+------------+--------+

求每日总sales时

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2022-01-10 |    300.00 |
| 2022-01-13 |     50.00 |
| 2022-01-15 |    180.00 |
| 2022-01-17 |      5.00 |
+------------+-----------+

这样产生的结果, 中间会缺少部分日期.
先写个 sql, 根据日期, 输出中间的日期列表:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

+------------+
| date       |
+------------+
| 2022-01-10 |
| 2022-01-11 |
| 2022-01-12 |
| 2022-01-13 |
| 2022-01-14 |
| 2022-01-15 |
| 2022-01-16 |
| 2022-01-17 |
+------------+

结合上述 sql:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2022-01-10 |    300.00 |
| 2022-01-11 |      0.00 |
| 2022-01-12 |      0.00 |
| 2022-01-13 |     50.00 |
| 2022-01-14 |      0.00 |
| 2022-01-15 |    180.00 |
| 2022-01-16 |      0.00 |
| 2022-01-17 |      5.00 |
+------------+-----------+

分层数据遍历

简单写个 sql, 建表并插入数据

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(117, "Zzs", NULL),      # zzs is the boss (manager_id is NULL)
(198, "John", 117),      # John has ID 198 and reports to 117 (zzs)
(692, "Tarek", 117),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

此时数据库内数据如下:

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  117 | Zzs     |       NULL |
|  123 | Adil    |        692 |
|  198 | John    |        117 |
|  692 | Tarek   |        117 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

通过以下 sql, 查询出管理链路:

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

查询结果如下:

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  117 | Zzs     | 117             |
|  198 | John    | 117,198         |
|   29 | Pedro   | 117,198,29      |
| 4610 | Sarah   | 117,198,29,4610 |
|   72 | Pierre  | 117,198,29,72   |
|  692 | Tarek   | 117,692         |
|  123 | Adil    | 117,692,123     |
+------+---------+-----------------+

参考文档

WITH (Common Table Expressions)

zzsan
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql8 公用表表达式CTE的使用方法实例分析
01-19
本文实例讲述了mysql8 公用表表达式CTE的使用方法。分享给大家供大家参考，具体如下： 公用表表达式CTE就是命名的临时结果集，作用范围是当前语句。 说白点你可以理解成一个可以复用的子查询，当然跟子查询还是有点...
新特性解读 | MySQL 8.0 通用表达式（WITH）深入用法
ActionTech的博客
05-19 3066
作者：杨涛涛 资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 MySQL 8.0 发布已经好几年了，之前介绍过 WITH 语句（通用表达式）的简单用途以及使用场景，类似如下的语句.
[Mysql] CAST函数
热门推荐
Hudas的博客
04-27 8万+
本文主要介绍Mysql中的CAST函数
mysql -- WITH RECURSIVE 语法
最新发布
dazhong2012的专栏
05-10 893
在 SQL 中，WITH RECURSIVE 是一个用于创建递归查询的语句。它允许你定义一个 Common Table Expression (CTE)，该 CTE 可以引用自身的输出。递归 CTE 非常适合于查询具有层次结构或树状结构的数据，例如组织结构、文件系统或任何其他具有自引用关系的数据。
mysql支持with语句_MySQL的SQL语句 - 数据操作语句（17）- WITH 语句（2）
weixin_39866963的博客
02-18 351
递归公共表表达式递归公共表表达式是具有引用其自身名称的子查询的表达式。例如：1. WITH RECURSIVE cte (n) AS2. (3. SELECT 14. UNION ALL5. SELECT n + 1 FROM cte WHERE n < 56. )7. SELECT * FROM cte;执行时，语句将生成以下结果，即一个包含简单线性序列的列：1. +-----...
MySQL 的 WITH 语法
howeres的博客
12-23 3万+
WITH AS 短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断 with t as ( select * from consumer ) select * from t
MySQL 中 WITH 用法详解
qq_45443475的博客
07-21 1万+
WITH 子句的主要用途是创建一个暂时的结果集，这个结果集在后续的查询中可以多次使用。WITH 子句主要用于解决查询复杂度高的问题，因为它可以将多次需要的计算结果集存储下来，以便后续的查询可以直接使用。WITH 子句的主要优点是允许开发人员将复杂的查询进行分解和抽象，以提高代码的可读性和可维护性。例如，如果需要对同一表的不同字段进行多次查询，可以使用 WITH 子句将这些查询的结果集抽象为一个独立的部分，从而避免代码的重复。WITH 子句是 MySQL 中的语句，可以为查询提供临时表。
MYSQL 的WITH用法
一个想交朋友的陌生人
11-07 1494
MYSQL 的WITH用法
mysql支持with语句_MySQL with语句小结
weixin_39901518的博客
02-10 6578
备注:测试数据库版本为MySQL 8.0这个blog我们来聊聊MySQL的with语句对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。如需要scott用户下建表及录入数据语句，可参考:scott建表及录入数据sql脚本语句结构:with subquery_name1 as (s...
with函数使用
weixin_44464850的博客
05-17 1521
with函数
MySQL实现递归查询的三种方式.rar
07-29
### 方式三：MySQL 8及以上版本的 `WITH RECURSIVE` 语句 从MySQL 8.0开始，引入了`WITH RECURSIVE`子句，这是一种标准的SQL语法，专门用于执行递归查询。它允许在单个查询中定义一个临时的、可递归的表，这极大地...
教你如何使用MySQL8递归的方法
09-08
WITH RECURSIVE cte_name AS ( SELECT ... -- return initial row set UNION ALL / UNION DISTINCT SELECT ... -- return additional row sets ) SELECT * FROM cte; ``` 在这个结构中，`cte_name`是你定义的CTE...
mysql.rar_MYSQL
09-19
在MySQL中，可以使用`WITH RECURSIVE`语句来实现递归查询。例如，`student_demo.sql`可能包含一个关于如何通过递归查询遍历学生和其所属班级层次的示例。 2. **约束（Constraints）**： `constraint.sql`可能涵盖...
MySQL with语句小结
只是甲的博客
06-02 4万+
备注:测试数据库版本为MySQL 8.0 这个blog我们来聊聊MySQL的with语句 对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性 MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。 如需要scott用户下建表及录入数据语句，可参考: scott建表及录入数据sql脚本 语句结构: with subquery_name1 as (subquery_body1), subquery_name2 as (subque
mysql with 查询_mysql笔记(6)-多表查询之with
weixin_39668479的博客
01-18 586
本文将简单介绍with子句的格式和用法1、目的通过创建一张(或多张)临时表，实现数据的抽离，从而方便进行数据的比较2、格式//创建2张临时表，并在这2张临时表的基础上进行查询withtemporary_table1_name(column_name,column_name) as(select xxxfrom xxx),temporary_table2_name(column_name,colum...
MySQL with 写法
乔木晨子的博客
05-12 2万+
1.with 写法，让两个表的相同等级人数相除 统计出两张表每个等级的人数，再使用等级进行join，再计算。 with t1 as ( select emp.emp_level as type, count(emp.id) as n from emp group by emp.emp_level ), t2 as ( select d.depot_level as type, count(d.id
With语句的应用
Sarah Cla的专栏
07-05 758
1.查询ID=2的部门下的所有下级部门：WITH GetDescendants(ID, DeptCode, DeptName, FatherID, fLevel)AS( SELECT ID, DeptCode, fName, FatherID, 0 asfLe
MySQL----with as用法
Mac_qq的博客
03-22 2712
3.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。但同一with子句内部不能嵌套with子句。
一篇文章成为递归大神：MySQL递归查询（with recursive）
w13966597931的博客
09-24 2万+
文章包含了MySQL with Recursive 语法的概念，结合两个常用的查询案例，分析具体使用。 其中包括 向上递归案例，向下递归案例。 最后是 注意事项 和 总结
mysql with recursive 递归调用
08-05
MySQL的WITH RECURSIVE语法可以实现递归查询。具体用法如下所示： ```sqlWITH RECURSIVE cte_name (col_name [, col_name ...) AS ( subquery [, cte_name (col_name [, col_name ...) AS (subquery)] ... ) SELECT * FROM cte_name [ORDER BY ...]; ``` 其中，cte_name是递归查询的名称，col_name是要查询的列名，subquery是子查询的语句。 如果需要向上递归查询，可以参考以下示例： ```sqlWITH RECURSIVE t1 AS ( SELECT * FROM course_category p WHERE id = '1-1-1' UNION ALL SELECT t.* FROM course_category t INNER JOIN t1 ON t1.parentid = t.id) SELECT * FROM t1 ORDER BY t1.id, t1.orderby; ``` 这个例子中，通过递归查询父级课程分类，从'id'为'1-1-1'开始，一直向上查询，直到顶级分类。 如果需要实现课程分类的查询，可以参考以下示例： ```sqlWITH RECURSIVE t1 AS ( SELECT * FROM course_category p WHERE id = '1' UNION ALL SELECT t.* FROM course_category t INNER JOIN t1 ON t1.id = t.parentid) SELECT * FROM t1 ORDER BY t1.id, t1.orderby; ``` 这个例子中，通过递归查询子级课程分类，从'id'为'1'开始，一直向下查询，直到最底层分类。 请根据实际需求，适当修改语句中的表名、列名和条件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值