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
?