mysql 嵌套集合_mysql 基于嵌套集合(Nested Set)模型的 path查找 sql函数

2.empId

附上代码：

BEGIN

DECLARE path VARCHAR(255);

DECLARE parentId INT(11);

DECLARE curDeptId INT(11);

DECLARE pId INT(11);

SET path=CONCAT(‘/‘,deptId);

SET curDeptId=deptId;

SELECT p.id INTO parentId FROM dept node,dept p

WHERE node.lft BETWEEN p.lft AND p.rgt AND node.id!=p.id AND node.id = curDeptId

ORDER BY p.lft DESC LIMIT 1;

IF(parentId>0) THEN

SET path=CONCAT(parentId,path);

END IF;

LOOP1:WHILE(parentId IS NOT NULL AND parentId>1) DO

SELECT parent.id INTO pId FROM dept node,dept parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id != parent.id

AND node.id = parentId

ORDER BY parent.lft DESC

LIMIT 1;

IF(pId>0) THEN

SET path=CONCAT(pId,‘/‘,path);

SET parentId=pId;

SET pId=NULL;

END IF;

END WHILE LOOP1;

RETURN(path);

END

以上代码是根据deptId查找其自身的path路径

##---------------------------------------------------------

BEGIN

DECLARE path VARCHAR(255);

DECLARE parentId INT(11);

DECLARE curDeptId INT(11);

DECLARE pIdINT(11);

SET path=CONCAT(‘/‘,empId);

SET curDeptId=empId;

SELECT emp.dept_id INTO parentId FROM ep emp WHERE emp.id = curDeptId;

IF(parentId>0) THEN

SET path=CONCAT(parentId,path);

END IF;

LOOP1:WHILE(parentId IS NOT NULL AND parentId>1) DO

SELECT parent.id INTO pId FROM dept node,dept parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id != parent.id

AND node.id = parentId

ORDER BY parent.id DESC

LIMIT 1;

IF(pId>0) THEN

SET path=CONCAT(pId,‘/‘,path);

SET parentId=pId;

SET pId=NULL;

END IF;

END WHILE LOOP1;

RETURN(path);

END以上代码是根据所在部门的emp员工的path路径。

原文：http://blog.csdn.net/skyshowshow/article/details/18444499