mysql查询某个节点的所有子节点,不包括自身
SELECT
id ,parent_id,name
FROM
(
SELECT
t1.id,t1.name,t1.parent_id,
IF
( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), -1) AS ischild
FROM
( SELECT id, parent_id,name FROM #表名 t) t1,
( SELECT @pids := #父节点id ) t2
) t3
WHERE
ischild != -1
mysql查询某个节点的所有子节点,包括自身
SELECT
id ,parent_id,name
FROM
(
SELECT
t1.id,t1.name,t1.parent_id,
IF
( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), IF(t1.id = #父节点id, #父节点id, -1) ) AS ischild
FROM
( SELECT id, parent_id,name FROM #表名 t) t1,
( SELECT @pids := #父节点id ) t2
) t3
WHERE
ischild != -1
mysql查询某些节点的所有子节点,包括自身
SELECT
id ,parent_id,name
FROM
(
SELECT
t1.id,t1.name,t1.parent_id,
IF
( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), IF(find_in_set( t1.id, @pids )>0, t1.id, -1) ) AS ischild
FROM
( SELECT id, parent_id,name FROM #表名 t) t1,
( SELECT @pids := “#父节点id1,#父节点id2,.....” ) t2
) t3
WHERE
ischild != -1