计算表中层次结构数据的所有子节点 [英] Count all child nodes of hierarchical data in a table
问题描述
我想使用邻接模型(父子项)对表中维护的任何树结构级别下的所有子节点数进行计数.表结构和数据如下所示:
I want to count number of all child nodes under any level of tree structure maintained in a table using adjacency model (parent-child key). Table structure and data looks like this:
id - item- parentid
1 - A -
2 - B - 1
3 - C - 1
4 - D - 2
5 - E - 2
6 - F - 3
7 - G - 3
8 - H - 5
9 - I - 5
10 - J - 9
11 - K - 4
例如,B具有以下子代和大子代结构:
For example B has following child and grand child structure:
- B
- E
- H
- 我
- J
- B
- E
- H
- I
- J
- K
- K
现在,如果您要计算"B的所有子节点",我的答案应为6.
Now if you want to count "All child nodes of B" my answer should be 6.
任何基于纯SQL Query的解决方案都会有很大帮助.否则mysql/php也将起作用.
Any pure SQL Query based solution would be of great help. Or mysql/php will also work.
谢谢!
推荐答案
可以使用非递归存储过程相当简单地完成以下操作:
Can be done fairly simply with a non-recursive stored procedure as follows:
示例通话
mysql> call category_hier(1); +--------------+ | num_children | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call category_hier(2); +--------------+ | num_children | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
完整脚本
drop table if exists categories; create table categories ( cat_id smallint unsigned not null auto_increment primary key, name varchar(255) not null, parent_cat_id smallint unsigned null, key (parent_cat_id) ) engine = innodb; insert into categories (name, parent_cat_id) values ('Location',null), ('Color',null), ('USA',1), ('Illinois',3), ('Chicago',3), ('Black',2), ('Red',2); drop procedure if exists category_hier; delimiter # create procedure category_hier ( in p_cat_id smallint unsigned ) begin declare v_done tinyint unsigned default 0; declare v_depth smallint unsigned default 0; create temporary table hier( parent_cat_id smallint unsigned, cat_id smallint unsigned, depth smallint unsigned default 0 )engine = memory; insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id; create temporary table tmp engine=memory select * from hier; /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ while not v_done do if exists( select 1 from categories c inner join tmp on c.parent_cat_id = tmp.cat_id and tmp.depth = v_depth) then insert into hier select c.parent_cat_id, c.cat_id, v_depth + 1 from categories c inner join tmp on c.parent_cat_id = tmp.cat_id and tmp.depth = v_depth; set v_depth = v_depth + 1; truncate table tmp; insert into tmp select * from hier where depth = v_depth; else set v_done = 1; end if; end while; /* select c.cat_id, c.name as category_name, p.cat_id as parent_cat_id, p.name as parent_category_name, hier.depth from hier inner join categories c on hier.cat_id = c.cat_id left outer join categories p on hier.parent_cat_id = p.cat_id order by hier.depth; */ select count(*) as num_children from hier where parent_cat_id is not null; drop temporary table if exists hier; drop temporary table if exists tmp; end # delimiter ; call category_hier(1); call category_hier(2);
您可以轻松调整此示例以适合您的要求.
You can easily adapt this example to suit your requirements.
希望它会有所帮助:)
这篇关于计算表中层次结构数据的所有子节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- E
- E