在实现F2工作流底层多数据库支持时发现Oracel和mssql都有提供递归子查询,而MySql却没有,没办法需要自己构建存储过程来提供这个递归子查询的功能。
-- 当前节点及子节点
-- 参数说明:idd 要查询的资源ID值,idFieldName ID字段名,parentIdFieldName 上级ID字段名,tableName 表名,isContainMySelf 是否包含自己
DROP procedure IF EXISTS pro_getChildrenList;
create procedure pro_getChildrenList(in idd varchar(100),in idFieldName varchar(100),in parentIdFieldName varchar(100),in tableName varchar(100),isContainMySelf int)
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(ID VARCHAR(100),ParentID varchar(100) ,levv INT);
set @strsql = CONCAT('INSERT tmp1' ,' SELECT ',idFieldName ,',' ,parentIdFieldName,',',1, ' FROM ', tableName,' WHERE ',parentIdFieldName,'=','''', idd,'''');
prepare strsql from @strsql;
execute strsql;
while row_count()>0
do
set lev=lev+1;
set @strsql = CONCAT('INSERT tmp1' ,' SELECT ','t.',idFieldName,',' ,' t.',parentIdFieldName,',' ,lev, ' FROM ', tableName,' t join tmp1 a on ','t.',parentIdFieldName,'=a.ID And levv=', lev-1);
prepare strsql from @strsql;
execute strsql;
end while ; SET @myself='';
IF (isContainMySelf=1) THEN
SET @myself = concat(' or ',idFieldName,'=','''',idd,'''');
ELSE
SET @myself = '';
END IF; set @strsql = CONCAT('SELECT * from ', tableName,' where ',idFieldName,' in ( SELECT ID from tmp1)',@myself);
prepare strsql from @strsql;
execute strsql;
end;