MySQL递归查询

目录

前言

一、向上递归（包含⾃⾝）

二、向下递归（不含自身）

三、向下递归（包含自身）

总结

---

前言

个人总结测试完整的MySQL递归查询语句，仅供参考！

一、向上递归（包含⾃⾝）

SELECT DISTINCT

        T2.dept_id

FROM

        ( SELECT DISTINCT

                @r AS t1_id,

                ( SELECT @r := PARENT_ID FROM sys_dept WHERE dept_id = t1_id ) AS                 PARENT_ID FROM ( SELECT @r := 208 ) vars, sys_dept h

                WHERE @r <> - 1 ) T1

JOIN sys_dept T2 ON T1.t1_id = T2.dept_id

二、向下递归（不含自身）

SELECT

        dept_id

FROM

        ( SELECT t1.dept_id, IF ( find_in_set( parent_id, @pid ) > 0, @pid := concat( @pid, ',',         dept_id ), - 1 ) AS ischild FROM ( SELECT dept_id, parent_id FROM sys_dept ) t1, (         SELECT @pid := 100 ) t2 ) t3

WHERE

        ischild != -1

三、向下递归（包含自身）

SELECT

        dept_id

FROM

        ( SELECT t1.dept_id, IF ( find_in_set( parent_id, @pid ) > 0, @pid := concat( @pid, ',',         dept_id ), - 1 ) AS ischild, IF ( find_in_set( dept_id, @pid )> 0, @pid := concat( @pid,         ',', dept_id ), - 1 ) AS oneself FROM ( SELECT dept_id, parent_id FROM sys_dept ) t1,         ( SELECT @pid := 200 ) t2 ) t3

WHERE

        ischild != - 1 OR oneself != - 1

---

总结

sys_dept：表名

dept_id：主键ID

parent_id：关联父ID

使用简单的单表递归查询。