MySql 中查询树形结构的全部子项列表 Function

不包含当前节点的Function

?

CREATE  FUNCTION `linkPositionId`(`nodeId` varchar(200)) RETURNS varchar(500) CHARSET utf8BEGINdeclare tmpPositionId varchar(100);declare positionIdStr  varchar(500);set positionIdStr = '';select LEADER_POSITION_ID,POSITION_ID into nodeId,tmpPositionId from HRM_POSITION where POSITION_ID = nodeId;while nodeId is not null doselect LEADER_POSITION_ID,POSITION_ID into nodeId,tmpPositionId from HRM_POSITION where POSITION_ID = nodeId;set positionIdStr = concat('$',tmpPositionId,'$',positionIdStr);end while;return positionIdStr;END;

?

包含当前节点的Function

?

CREATE FUNCTION `linkPositionIdIncludeSelf`(`nodeId` varchar(200)) RETURNS varchar(500) CHARSET utf8BEGINdeclare tmpPositionId varchar(100);declare positionIdStr  varchar(500);set positionIdStr = '';while nodeId is not null doselect LEADER_POSITION_ID,POSITION_ID into nodeId,tmpPositionId from HRM_POSITION where POSITION_ID = nodeId;set positionIdStr = concat('$',tmpPositionId,'$',positionIdStr);end while;return positionIdStr;END;

?