mysql 8.0 递归处理 （sample database classicmodels _No.4）
准备工作，可以去下载 classicmodels 数据库资源如下
[ 点击：classicmodels]
(https://download.csdn.net/download/tomxjc/88685970)
前言
我们在做数据的时候 会碰到 部门表 ，员工表，这个一般是树型结构。我们先看下这个 全表的结构
一、查文档（recursion ）
当然还是 看 mysql 文档了。
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursion-limits
recursion 中文解释就是 递归或者循环
二、准备
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
截图结果就是 如下，这个基本就是数仓的结构了。
总结
以上就是一些分享，谢谢大家喜欢 ，后面还是 有其他关于classicmodels 例子 。
