java 处理sql递归查询_sql和mysql递归查询子节点

//SQL SqlServer:CTE函数处理递归(WITH语法) cte可以其他名字 注意匹配(所有cte全部替换)

with cte as

(

select a.typeid,a.superid,a.typename from ctype a where typeid=1

union all

select k.typeid,k.superid,k.typename from ctype k inner join cte c on c.typeid = k.superid

)select * from cte

//MYSQL 放到命令行执行

CREATE FUNCTION `getChildDeptLst`(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';

SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO

SET sTemp = concat(sTemp,',',sTempChd);

SELECT group_concat(id) INTO sTempChd FROM department where FIND_IN_SET(parentId,sTempChd)>0;

END WHILE;

RETURN sTemp;

END

select getChildLst(1);

select * from departmentg where FIND_IN_SET(id, getChildDeptLst(1));

//

//查询子项父id

SELECT T2.id, T2 .name FROM ( SELECT @r AS _id, (SELECT @r := parentId FROM department WHERE id = _id) AS parentId, @l := @l + 1 AS lvl FROM (SELECT @r := 30, @l := 0) vars, department h WHERE @r <> 0) T1 JOIN department T2 ON T1._id = T2.id ORDER BY T1.lvl DESC