mysql 部门 递归 新方法

SELECT  *  FROM  w_department

/*查询市场部 下 所以 部门 的部门名称*/
SELECT node.department_id,node.department_name
FROM w_department AS node,
w_department AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.department_name = '市场部'
ORDER BY node.lft;

/*查看所有的子部门  是父部门的不显示*/
SELECT department_id,department_name
FROM w_department
WHERE rgt = lft + 1;

/*查看所有的父部门  不包含子部门*/
SELECT parent.department_id,parent.department_name
FROM w_department AS node,
w_department AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.department_name = '市场部'
ORDER BY parent.lft;

 

 

/*查询 部门的级别   0,1,2 树的层级  ，层级深度*/
SELECT node.department_name, (COUNT(parent.department_name) - 1) AS depth
FROM w_department AS node,
w_department AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.department_name
ORDER BY node.lft;

 

/* 根据 部门名称   获取 该部门及下属部门 层级深度*/
/*这个查询语句可以检索出任一节点子树的深度值，包括根节点。这里的深度值跟你指定的节点有关。*/
SELECT node.department_name, (COUNT(parent.department_name) - (sub_tree.depth + 1)) AS depth
FROM w_department AS node,
 w_department AS parent,
 w_department AS sub_parent,
 (
  SELECT node.department_name, (COUNT(parent.department_name) - 1) AS depth
  FROM w_department AS node,
  w_department AS parent
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
  AND node.department_name = '总办'
  GROUP BY node.department_name
  ORDER BY node.lft
 )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
 AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
 AND sub_parent.department_name = sub_tree.department_name
GROUP BY node.department_name
ORDER BY node.lft;

 

 

/* 我们可以根据depth值来缩进分类名字，使用CONCAT和REPEAT字符串函数:*/
/*当然，在客户端应用程序中你可能会用depth值来直接展示数据的层次。Web开发者会遍历该树，随着depth值的增加和减少来添加<li></li>和<ul></ul>标签。*/
SELECT CONCAT( REPEAT(' ', COUNT(parent.department_name) - 1), node.department_name) AS NAME
FROM w_department AS node,
w_department AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.department_name
ORDER BY node.lft;

 

/*根据部门名称 查询 该部门 与第一层级部门的信息*/
SELECT node.department_name, (COUNT(parent.department_name) - (sub_tree.depth + 1)) AS depth
FROM w_department AS node,
 w_department AS parent,
 w_department AS sub_parent,
 (
  SELECT node.department_name, (COUNT(parent.department_name) - 1) AS depth
  FROM w_department AS node,
  w_department AS parent
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
  AND node.department_name = '总办'
  GROUP BY node.department_name
  ORDER BY node.lft
 )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
 AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
 AND sub_parent.department_name = sub_tree.department_name
GROUP BY node.department_name
HAVING depth <= 1
ORDER BY node.lft;

 

/*执行新增*/
CALL  pro_add_dep('01587bc0c3264dc0b6555fdb77204d83','财务1部','cw1','','','','',NULL,'','0b393bf7589f42f4b06e2cbd507b45e2','0b393bf7589f42f4b06e2cbd507b45e2','2015-12-08 15:02:51','2015-12-08 15:03:00','fc5bdb145d884cd2a65880cac43dd994','69985e19c50d47e3b16a86401d26aade');
/*执行删除*/
CALL pro_del_dep('01587bc0c3264dc0b645afdb77204d81');

SELECT * FROM w_department