今天头提了一个需求,要求 求一棵只知道父节点的树下有多少叶子节点,叶子节点有可能挂在中间节点上。
(怕说的不明确:求一个部门下的所有人,部门下可能有子部门,一级部门下有人,二级部门下也可能有人。
已知条件:1.已知人所属的部门 2.知道部门的父节点 3.部门节点的层数(无用))
我的设计思想: 希望有其他思路的同学可以踊跃发言,谢谢
1.利用广搜的思路,额外开辟一个空间记录哪些部门节点被遍历了。
2.将该节点下所有的子节点加入到额外空间中,并标识为未访问。
3.将该节点下的所有人加入到总人数中,将该节点标识为已访问。
4.直到所有节点标识显示都被访问。(所有都被访问表示树被遍历完成,因为:新加入的节点都是未访问的)
下面以SQL中的存储过程的形式 给出答案
不熟悉存储过程的同学可以参看我的存储过程讲解,传送门:http://blog.csdn.net/u010003835/article/details/50496904
- DROP PROCEDURE IF EXISTS calShouldNum;
-
-
- CREATE PROCEDURE calShouldNum(
- IN parent_dep_id INT(40),
- INOUT should_num INT
- )
- BEGIN
-
-
-
- DECLARE total_num INT DEFAULT 0;
- DECLARE tmp_dep_id INT(40) DEFAULT 0;
- DECLARE tmp_count INT DEFAULT 0;
-
-
- CREATE TABLE IF NOT EXISTS tmpCalShouldTable
- (
- department_num INT(40),
- should_mark INT DEFAULT 0
- );
-
-
- set max_sp_recursion_depth=254;
-
-
- SELECT COUNT(tmpCalShouldTable.department_num) FROM tmpCalShouldTable WHERE should_mark = 0 INTO tmp_count;
-
- WHILE tmp_count <> 0 DO
-
- SELECT department_num FROM tmpCalShouldTable WHERE tmpCalShouldTable.should_mark = 0 LIMIT 1 OFFSET 0 INTO tmp_dep_id;
-
- SELECT COUNT(u_info.user_id) FROM user_info u_info WHERE u_info.department_id = tmp_dep_id INTO total_num;
- SET should_num = total_num + should_num;
- UPDATE tmpCalShouldTable SET should_mark = 1 WHERE department_num = tmp_dep_id;
-
- INSERT INTO tmpCalShouldTable(department_num)
- SELECT u_dep.department_num FROM user_department u_dep
- WHERE u_dep.parent_id = parent_dep_id;
-
- SELECT department_num FROM tmpCalShouldTable WHERE tmpCalShouldTable.should_mark = 0 LIMIT 1 OFFSET 0 INTO tmp_dep_id;
- CALL calShouldNum(tmp_dep_id, should_num);
-
- SELECT COUNT(tmpCalShouldTable.department_num) FROM tmpCalShouldTable WHERE should_mark = 0 INTO tmp_count;
- END WHILE;
- END;