Mysql 递归计数和选择

Question

抱歉，我看到很多与此相关的问题，但我不明白更简单的方法。

我有:id|parentId|姓名

• 如何统计某个 ID 的“儿子”数量？
• 如何进行选择以返回 id 的每个子节点及其“树级别”。

Answer 1

编辑:最初，我最顶层节点的parent_id为NULL。那是不正确的。它应该为零才能起作用(据我所知)。我发布了包含 NULL 的原始插入语句，但在工作时我必须将这些值更新为 0。我更正了下面的测试数据......

这样:http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

我做了这个...有点像你想要的。

mysql> describe table_name;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| parent_id | int(11)     | YES  |     | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+ 

insert into table_name values (1, 0, 'one');    -- was parent_id null, was wrong, now zer
insert into table_name values (2, 1, 'one one'); 
insert into table_name values (3, 2, 'one one one'); 
insert into table_name values (4, 2, 'one one two'); 
insert into table_name values (5, 2, 'one one three'); 
insert into table_name values (6, 5, 'one one three one'); 
insert into table_name values (7, 1, 'one two'); 
insert into table_name values (8, 0, 'two');    -- was parent_id null, was wrong, now zero
insert into table_name values (9, 8, 'two one');

delimiter //

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    table_name
                WHERE   parent_id = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent_id
                INTO    _id, _parent
                FROM    table_name
                WHERE   id = _parent;
        END LOOP;       
END
//
delimiter ;


SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, 
        parent_id, level, name
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, table_name
        WHERE   @id IS NOT NULL
        ) ho
JOIN    table_name hi
ON      hi.id = ho.id;


 +---------------+-----------+-------+-------------------+
| treeitem      | parent_id | level | name              |
+---------------+-----------+-------+-------------------+
| 1             |         0 |     1 | one               |
|     2         |         1 |     2 | one one           |
|         3     |         2 |     3 | one one one       |
|         4     |         2 |     3 | one one two       |
|         5     |         2 |     3 | one one three     |
|             6 |         5 |     4 | one one three one |
|     7         |         1 |     2 | one two           |
| 8             |         0 |     1 | two               |
|     9         |         8 |     2 | two one           |
+---------------+-----------+-------+-------------------+

k...如果你想计算所有这些困惑的后代...你需要这样做:

select  count(*)  
from   (select  hierarchy_connect_by_parent_eq_prior_id(id) as id, @level as level 
        from   (
               select  @start_with := 8,  --> ID of top node you want to count under
                       @id := @start_with,
                       @level := 0
               ) vars, table_name
        where  @id is not null  
       ) as x 
where id is not null  --> exclude that top node from the count