树形结构表记录 ：sql递归查询

数据表：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 ;