[SQL] 테이블에 계층 적 데이터의 모든 자식 노드를 계산

테이블에 계층 적 데이터의 모든 자식 노드를 계산

나는 인접 모델 (부모 - 자식 키)를 사용하여 테이블에서 유지 트리 구조의 모든 수준에서 모든 자식 노드의 수를 계산합니다. 이 같은 테이블 구조와 데이터 외모 :

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를 들어 다음과 같은 아동과 그랜드 아이 구조를 가지고 :

이제 "B의 모든 자식 노드"를 계산하려면 내 대답은 6이어야합니다.

모든 순수한 SQL 쿼리 기반 솔루션은 큰 도움이 될 것입니다. 또는 MySQL은 / ​​PHP는 작업도 않습니다.

감사!

해결법

1. ==============================

   1.당신이 당신의 데이터를 저장하는 방법은 전체 자녀 수를 얻을 수있는 간단한 쿼리를 허용하지 않습니다. 그러나 한 번 봐 가지고 :

   당신이 당신의 데이터를 저장하는 방법은 전체 자녀 수를 얻을 수있는 간단한 쿼리를 허용하지 않습니다. 그러나 한 번 봐 가지고 :

   http://en.wikipedia.org/wiki/Nested_set_model

   어디에서이 같은 쿼리가 가능하다.

2. ==============================

   2.다음과 같은 비 재귀 저장 프로 시저로 매우 간단하게 수행 할 수 있습니다 :

   다음과 같은 비 재귀 저장 프로 시저로 매우 간단하게 수행 할 수 있습니다 :

   예 통화

   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);
   

   당신은 쉽게 당신의 요구 사항에 맞게이 예제를 적용 할 수 있습니다.

   희망이 도움이 :)

3. ==============================

   3.다음은 PHP 기반의 솔루션입니다 :

   다음은 PHP 기반의 솔루션입니다 :

   function countChildren($startId) {
       $directDescendents = *_query("SELECT id FROM Table WHERE parentid = ?", array( $startId ));
       $count = *_num_rows($directDescendents);
       while($row = *_fetch_array($directDescendents))
           $count += countChildren($row['id']);
       return $count;
   }
   
   $numChildren = countChildren(2); // Number of Children for 'B'
   

   * _num_rows 및 사용하는 SQL 확장을위한 어떤 기능 * _fetch_array를 교체합니다. 이것은 순수한 SQL 솔루션으로 효율적되지 않습니다,하지만 작동합니다. 나는 기능에 질의하고있어 방법은 바인딩 된 매개 변수를 가정하지만, 당신이 원하는대로 쿼리를 실행합니다.

from https://stackoverflow.com/questions/6061025/count-all-child-nodes-of-hierarchical-data-in-a-table by cc-by-sa and MIT license