树型表的特点就是它有一个标志性字段parentId,父类节点id,如果节点数是固定的可以使用表的自连接去查询
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表中初始的数据是id等于1的记录，即根结点。
通过inner join t1 t2 on t2.id = t.parentid 找到id='1'的下级节点 。
通过这种方法就找到了id='1'的所有下级节点，下级节点包括了所有层级的节点。
