论坛 › MySQL运维 › MySql 中查询树形结构的全部子项列表 Function

gdrocket 发表于 2016-10-24 10:06:12

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

　　不包含当前节点的Function
　　

CREATEFUNCTION `linkPositionId`(`nodeId` varchar(200))
RETURNS varchar(500) CHARSET utf8
BEGIN
declare tmpPositionId varchar(100);
declare positionIdStrvarchar(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 positionIdStrvarchar(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;

　　

查看完整版本: MySql 中查询树形结构的全部子项列表 Function