数据表:t_task (task_id , task_name , group_id , parent_id)
postgresql ----------
1:已知task_id 获取所有的子节点:
WITH RECURSIVE result AS (
SELECT
task_id,
task_name,
group_id,
parent_id
FROM t_task tc
WHERE task_id=#{taskId}
UNION ALL
SELECT
tt.task_id,
tt.task_name,
tt.group_id,
tt.parent_id
FROM t_task tt,result
WHERE tt.parent_id = result.task_id
) select
task_id as taskId ,
task_name as taskName,
group_id as groupId ,
parent_id as parentId
from result
2:已知task_id 获取该任务的父节点路径:
WITH RECURSIVE result AS (
SELECT
task_id,
task_name,
group_id,
parent_id
FROM t_task tc
WHERE task_id=#{taskId}
UNION ALL
SELECT
tt.task_id,
tt.task_name,
tt.group_id,
tt.parent_id
FROM t_task tt,result
WHERE tt.task_id = result.parent_id
) select
task_id as taskId ,
task_name as taskName,
group_id as groupId ,
parent_id as parentId
from result
postgresql end-------
mysql ---------
需要用函数来实现
已知表名 : t_target_package_dir 字段名(id, parent_id)
CREATE FUNCTION `queryAllTreeIdByParentId`(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 t_target_package_dir where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN substring(sTemp,3);
END
select queryAllTreeIdByParentId(0);
mysql end-----
获取所有父节点
CREATE FUNCTION `queryAllParentTreeIdById`(cId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(cId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(categorys_pid) INTO sTempChd FROM t_categorys where FIND_IN_SET(id,sTempChd)>0;
END WHILE;
RETURN substring(sTemp,3);
END$$
DELIMITER ;