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;