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

来源：互联网发布：alibaba json jar 编辑：程序博客网时间：2024/06/05 02:20

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

2.empId

附上代码：

BEGINDECLARE 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 = curDeptIdORDER BY p.lft DESC LIMIT 1;IF(parentId>0) THENSET path=CONCAT(parentId,path);END IF;LOOP1:WHILE(parentId IS NOT NULL AND parentId>1) DOSELECT parent.id INTO pId FROM dept node,dept parentWHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id != parent.idAND node.id = parentIdORDER BY parent.lft DESCLIMIT 1;IF(pId>0) THENSET path=CONCAT(pId,'/',path);SET parentId=pId;SET pId=NULL;END IF;END WHILE LOOP1;    RETURN(path);END

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

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

BEGINDECLARE 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) THENSET path=CONCAT(parentId,path);END IF;LOOP1:WHILE(parentId IS NOT NULL AND parentId>1) DOSELECT parent.id INTO pId FROM dept node,dept parentWHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id != parent.idAND node.id = parentIdORDER BY parent.id DESCLIMIT 1;IF(pId>0) THENSET path=CONCAT(pId,'/',path);SET parentId=pId;SET pId=NULL;END IF;END WHILE LOOP1;      RETURN(path);END

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

0 0