mysql 基于嵌套集合(Nested Set)模型的 path查找 sql函数
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路径。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。