树形表查询

ゝ一纸荒年。 2024-03-27 07:30 71阅读 0赞

树形表:

![a774602ae6404db2bf0a5d3e6bf8f88a.png][]

### 情况一:固定层级的树形表: ###

可以使用表的自连接查询,比如查询两级分类课程

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

### 情况二:树的层级不确定: ###

可以使用mysql的递归查询,使用with语法

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

cte\_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询

col\_name :公共表达式包含的列名,可以写也可以不写

#### 向下递归: ####

例子:

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

说明： t1 相当于一个表名

select 1 相当于这个表的初始值，这里使用UNION ALL 将初始值加入到表中。

t1表中初始的数据是id等于1的记录，即根结点。

通过inner join t1 t2 on t2.id = t.parentid 找到id='1'的下级节点 。

通过这种方法就找到了id='1'的所有下级节点，下级节点包括了所有层级的节点。 上边这种方法是向下递归，即找到初始节点的所有下级节点。

#### 向上递归: ####

with 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

初始节点为1-1-1，通过递归找到它的父级节点，父级节点包括所有级别的节点。

[a774602ae6404db2bf0a5d3e6bf8f88a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/cd416d2fcce7473999524abf5794b3c5.png