树形表查询

树形表:

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

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

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，通过递归找到它的父级节点，父级节点包括所有级别的节点。