mysql 树形汇总,MySql 中查询树形构造的全部子项列表 Function

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

不包含当前节点的Function

CREATE FUNCTION `linkPositionId`(`nodeId` varchar(200))

RETURNS varchar(500) CHARSET utf8

BEGIN

declare 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 do

select 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 utf8

BEGIN

declare tmpPositionId varchar(100);

declare positionIdStr varchar(500);

set positionIdStr = '';

while nodeId is not null do

select 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;