Mysql树形结构递归查询

1.表的基础数据

类似于这种三级目录：
– 1：根结点
– 1-1至1-11
– 1-1-1 至1-1-10

2.基础查询语句

可以使用内联查询inner join去查询：

SELECT
       one.id            one_id,
       one.name          one_name,
       one.parentid      one_parentid,
       one.orderby       one_orderby,
       one.label         one_label,
       two.id            two_id,
       two.name          two_name,
       two.parentid      two_parentid,
       two.orderby       two_orderby,
       two.label         two_label
   FROM course_category ONE
            INNER JOIN course_category two ON one.id = two.parentid
   WHERE one.parentid = 1
     AND one.is_show = 1
     AND two.is_show = 1
   ORDER BY one.orderby,
            two.orderby

查询结果：
可以看到并没有展示出根节点，而且如果出现多个嵌套的情况时，这个查询语句写起来就十分的冗余了。

3.Mysql8递归查询

（1）基本语法

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

其中RECURSIVE类似于一个虚拟表，其主要是根据下面的语句union 而成的。

（2）简单案例

with RECURSIVE t1  AS
(
## 确定初试的记录n=1
  SELECT 1 as n
  UNION ALL## 连接下面的查询结果
  SELECT n + 1 FROM t1 WHERE n < 5##从t1中查询结果n并将其加1，终止条件为n<5
)
SELECT * FROM t1;

（3）编写递归查询语句

WITH 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

上述语句是以1为根节点，向下递归遍历，这样就可以得到树形结构了。