orcale 递归查询子节点Mysql替换方法

Question

1.遇到问题(递归查询子节点时有两个pid如何更改)

2.附上oracle代码：

select * from dataclean_element t start with t.id=#{nodeId} CONNECT BY PR OR t.id in(t.src_id,t.tag_id)

3.报错信息:Subquery returns more than 1 row

4.自己改的mysql问题代码 -> create definer = syjl_test@% function getDataCleanElementChildList(rootId int) returns varchar(10000)
BEGIN
DECLARE sTemp VARCHAR(10000);
DECLARE sTempChd VARCHAR(10000);
DECLARE PID VARCHAR(10000);

SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
    SET sTemp = concat(sTemp,',',sTempChd);
    SET PID = (select SRC_ID from DATACLEAN_ELEMENT union select TAG_ID from DATACLEAN_ELEMENT);
    SELECT group_concat(id) INTO sTempChd FROM  DATACLEAN_ELEMENT where FIND_IN_SET(PID,sTempChd)>0;
END WHILE;
RETURN sTemp;

END;
5.博客参考 -> https://www.cnblogs.com/fakerblog/p/8372240.html

Answer 1

https://www.cnblogs.com/fakerblog/p/8372240.html