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;