计算表中层次结构数据的所有子节点 [英] Count all child nodes of hierarchical data in a table

查看:74
本文介绍了计算表中层次结构数据的所有子节点的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想使用邻接模型(父子项)对表中维护的任何树结构级别下的所有子节点数进行计数.表结构和数据如下所示:

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆