先看一下子数据库设计
SELECT
t3.dept_id,t3.name
FROM(SELECT t1.dept_id, t1.name,IF( find_in_set( pid, @pids ) > 0, @pids := concat( @pids, ',', dept_id ), 0 ) AS ischild
FROM( SELECT dept_id, pid, name FROM jskj_sys_depts t ORDER BY pid, dept_id ) t1,( SELECT @pids := #{dept_id} ) t2 ) t3
WHERE ischild != 0
如果没有子级id 可以查自己
SELECT
t3.pid,t3.NAME,
FROM(SELECT t1.pid, t1.NAME,IF( find_in_set(dept_id , @pids ) > 0, @pids := concat( @pids, ',', pid ), 0 ) AS ischild
FROM( SELECT pid, dept_id, NAME FROM jskj_sys_depts t ORDER BY dept_id, pid ) t1,( SELECT @pids := #{dept_id} ) t2 ) t3
WHERE ischild != 0