1语句中定义变量
SELECT
*
FROM
department
WHERE
id IN(
SELECT id
FROM department
WHERE id = 4
UNION(
SELECT id FROM
(
SELECT id,parent_id
FROM department
ORDER BY
parent_id,id
) depart_sorted,
(SELECT @pv := 4)
initialisation
WHERE find_in_set(parent_id,@pv)
AND length(@pv:=concat(@pv,',',id))
)
);
tips:如果id为字符串可能会出现 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) 错误
可以在find_in_set(parent_id,@pv 后添加 COLLATE utf8mb4_unicode_ci
2写数据库函数通过函数查询
函数
create function getRoles(roleId INT)
returns varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(roleId AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM yzm_talks_role WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
调用
SELECT *
FROM yzm_talks_role
WHERE FIND_IN_SET(parent_id,getRoles(#{id}))
3程序内处理
查询id,parant_id到程序中,通过程序处理
获取树形结构所有子节点
oracle中有connect by prior可以使用
