MySQL CTE递归语句示例

792

6页

0次

2020-06-28

5墨值下载

前言：在许多场景中需要直接加载一颗树或者某个树的一部分，多层级数据情况下，如果单用

数据库语句的情况下，需要用到 CTE 递归语句。

一、CTE 语法（ORACLE、MySQL 大同小异）：

WITH cte_name (column_list) AS (

query

)

SELECT * FROM cte_name;

注意：

请注意，查询中的列数必须与 column_list 中的列数相同。如果省略

column_list，CTE 将使用定义 CTE 的查询的列列表。

二、CTE 与递归的基本概念

2.1 CTE（公用表表达式，Common Table Expression）

CTE 是一个命名的临时结果集，仅在单个 SQL 语句（例如 SELECT，INSERT，UPDATE

或 DELETE）的执行范围内存在。

与派生表（子查询）类似，CTE 不作为对象存储，仅在查询执行期间持续。与派生表不同，

CTE 可以是自引用（递归 CTE），也可以在同一查询中多次引用。此外，与派生表相比，

CTE 提供了更好的可读性和性能。

在 Oracle 有些许不同，这种功能被称为“子查询因子化”（Subquery Factoring）。

示例：

WITH customers_in_usa AS (

SELECT

customerName, state

FROM

customers

WHERE

country = 'USA'

)

SELECT

customerName

FROM

customers_in_usa -- 第 1 次引用

WHERE

state = 'CA'

UNION ALL

SELECT

customerName

FROM

customers_in_usa -- 第 2 次引用

WHERE

state = 'LA'

2.2 递归

程序调用自身的编程技巧称为递归（ recursion）。上面的子查询中出现自身的别名就是一个

例子。一般来说，递归需要有边界条件（终止条件）、递归前进段（执行逻辑）和递归返回段

（终止执行并返回结果）。当边界条件不满足时，递归前进（继续执行）；当边界条件满足时，

递归返回（终止执行并返回结果）。

2.3 递归 CTE

综上所述，递归 CTE 是不断调用自己，直到满足终止条件才输出所有数据。终止条件一般是

“某一次查询的结果集没有数据”（类似于 exists 子句的效果）。

三、实现示例：

3.1 MySQL CTE 实现树查找

表结构：

CREATE TABLE `t_sys_organization` (

`org_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '组织机构 Id',

`org_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT

NULL COMMENT '组织机构名称',

`parent_id` int(11) unsigned NOT NULL COMMENT '父节点为空的是根机构',

`level` int(11) DEFAULT NULL COMMENT '组织层级',

`is_valid` int(11) DEFAULT NULL COMMENT '是否有效',

PRIMARY KEY (`org_id`),

UNIQUE KEY `idx_oname` (`org_name`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8

--实现查找根节点下面所有子节点并带着部门名称

with recursive t_rec as -- recursive 为关键字，可以不列出字段列表

(

select

m.org_id

,m.org_name

,m.parent_id

,m.level

,concat('->',m.org_id) as nodepath -- 拼接各结点的 id

,concat('->',m.org_name) as nodename

from t_sys_organization m

where org_id = 1

union all

select

t1.org_id

,t1.org_name

,t1.parent_id

,t1.level

of 6

5墨值下载

mysql

关注

评论