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

mysql 基于嵌套集合(Nested Set)模型的 path查找  sql函数，此两个函数可用于extjs4框架对于左侧treepanel的根据节点的path异步展开的情形。每个函数有一个参数。第一个
1.deptId

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 pId			INT(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