mysql 树结构查询

查询所有子节点

CREATE FUNCTION getChild(parentId VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN
    DECLARE oTemp VARCHAR(1000);
    DECLARE oTempChild VARCHAR(1000);
    SET oTemp = '';
    SET oTempChild = parentId;
    WHILE oTempChild is not null DO
        IF oTemp != '' THEN
            SET oTemp = concat(oTemp,',',oTempChild);
        ELSE
            SET oTemp = oTempChild;
        END IF;
        SELECT group_concat(ID) INTO oTempChild FROM mc_org where pid<>id and FIND_IN_SET(pid,oTempChild)>0;
    END WHILE;
RETURN oTemp;
END

查询祖先节点

SELECT T2.id,T2.org_name,T2.pid
FROM (
        SELECT
                @r AS _id,
                (SELECT @r := pid FROM mc_org WHERE id = _id) AS pid,
                @l := @l + 1 AS lvl
        FROM
                (SELECT @r := 18, @l := 0) vars,
                mc_org h
        WHERE @r <> 0) T1
JOIN mc_org T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC