- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

mysql 8.0 递归（例子分享）classicmodels _No.4

小Tomkk 发表于 2024/03/01 11:14:51 2024/03/01

【摘要】 mysql cte 递归

可以去下载 classicmodels 数据库资源如下

[[ 点击：classicmodels官网下载]]

[[也可以去CSDN 去免费资源下载]]

# 前言

我们在做数据的时候会碰到部门表，员工表，这个一般是树型结构。我们先看下这个全表的结构

# 一、查文档（recursion ）

当然还是看 mysql 文档了。

**recursion 中文解释就是递归或者循环**

# 二、准备

```sql
select * FROM classicmodels.employees t
可以理解  report to 就是 父ID 
``

# 三、递归处理

WITH RECURSIVE

WITH RECURSIVE cte (officeCode,employeeNumber,firstname,lastName, jobTitle,reportsTo ,tree,n ) AS (
    SELECT officeCode,employeeNumber,firstname,lastName, jobTitle,reportsTo, cast(employeeNumber as char(100)) as 'tree'
    , 1 as 'n'
    FROM classicmodels.employees where employeeNumber='1002'
    UNION all
    SELECT t.officeCode,t.employeeNumber,t.firstname,t.lastName, t.jobTitle ,t.reportsTo,
CONCAT(cn.tree , '->', t.employeeNumber) AS 'tree',n+1 as 'n'
    FROM classicmodels.employees t
  INNER JOIN cte cn ON t.reportsTo = cn.employeeNumber
)
SELECT a.employeeNumber,  CONCAT(a.firstname,',',a.lastName) as 'name',
a.jobTitle,a.reportsTo , a.tree,a.n  
FROM cte a

这个说明下 tree 列就是加的一列

一般的 RECURSIVE 是和 with 一起使用的。下面就是结果

也可以 将 
SELECT a.employeeNumber,  CONCAT(a.firstname,',',a.lastName) as 'name',
a.jobTitle,a.reportsTo , a.tree,a.n  
FROM cte a 


替换成 
SELECT a.employeeNumber,b.City,b.country,  CONCAT(a.firstname,',',a.lastName) as 'name',
a.jobTitle,a.reportsTo , a.tree,a.n  
FROM cte a left join classicmodels.offices b on a.officecode=b.officecode

***

# 四、提取

select tree,
if(n>=1, left(tree,4), null) as '1st',
if(n>=1 and length(tree)>4 ,  substring(tree,6,4), null) as '2st',
if(n>=1 and length(tree)>8 ,  substring(tree,11,4), null) as '3rd',
if(n>=1 and length(tree)>12 ,  substring(tree,16,4), null) as '4th'
from (


WITH RECURSIVE cte (officeCode,employeeNumber,firstname,lastName, jobTitle,reportsTo ,tree,n ) AS (
    SELECT officeCode,employeeNumber,firstname,lastName, jobTitle,reportsTo, cast(employeeNumber as char(100)) as 'tree'
    , 1 as 'n'
    FROM classicmodels.employees where employeeNumber='1002'
    UNION all
    SELECT t.officeCode,t.employeeNumber,t.firstname,t.lastName, t.jobTitle ,t.reportsTo,
CONCAT(cn.tree , ',', t.employeeNumber) AS 'tree',n+1 as 'n'
    FROM classicmodels.employees t
  INNER JOIN cte cn ON t.reportsTo = cn.employeeNumber
)
SELECT a.employeeNumber,  CONCAT(a.firstname,',',a.lastName) as 'name',
a.jobTitle,a.reportsTo , a.tree,a.n  
FROM cte a) dd

截图结果就是如下，这个基本就是数仓的结构了。