Oracle递归查询树替换成mysql递归查询树

Question

1.遇到问题(递归查询子节点时有两个pid如何更改)
2.附上oracle代码：(想在mysql做替换)
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;

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

6.将oracle 递归sql 改为mysql start with CONNECT BY PR OR t.id (t.src_id,t.tag_id)可行方案

Answer 1

https://www.cnblogs.com/mzq123/p/10381216.html