mysql 获得所有的子节点

mysql 获得所有的子节点

SELECT
    t3.id,t3.name,t3.pid
FROM
    (
    SELECT t1.id,
        IF ( find_in_set( t1.pid, @pids ) > 0, @pids := concat( @pids, ',', t1.id ), 0 ) AS ischild ,
        t1.name,t1.pid
FROM
    ( SELECT id, pid,NAME FROM dimension_tree t WHERE t.STATUS = 1 and t.isLeaf='0' ORDER BY pid, id ) t1,
    ( SELECT @pids := '1' id ) t2 
    ) t3 
WHERE
    t3.ischild != 0

 表结构：

 CREATE TABLE `dimension_tree` (
  `id` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '主键',
  `pid` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '父类目id',
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '节点名称'，
  `isLeaf` INT    COMMENT '是否是末节点 是否是末节点（0否1是）'
  PRIMARY KEY (`id`)
) 

结果：