<!--查询组织下面的所有子组织-->
<!--数据库先创建函数:如下-->
<!--
CREATE FUNCTION `getCatalogLst`(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 exp_catalog where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
-->
<select id="getCatalogByParentId" resultType="ExpCatalogVO">
SELECT * FROM EXP_CATALOG WHERE FIND_IN_SET(ID,getCatalogLst(#{parentId}));
</select>
<!--数据库先创建函数:如下-->
<!--
CREATE FUNCTION `getCatalogLst`(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 exp_catalog where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
-->
<select id="getCatalogByParentId" resultType="ExpCatalogVO">
SELECT * FROM EXP_CATALOG WHERE FIND_IN_SET(ID,getCatalogLst(#{parentId}));
</select>