mysql - 在具有停止条件的树上进行递归 SQL 查询以停止在子树中挖掘

Question

我在表中使用“帐户”树结构。

在这个例子中数据是:

 +---- +-----------+------+-----------------+| id  | parent_id | data | stop_descending |+---- +-----------+------+-----------------+|  1  |      NULL |    x |               0 ||  2  |         1 |    y |               0 ||  3  |         1 |    z |               1 ||  4  |         3 |    g |               0 ||  5  |         3 |    l |               0 ||  6  |         2 |    x |               0 ||  7  |         2 |    u |               1 ||  8  |         1 |    y |               1 ||  9  |         7 |    y |               0 ||  10 |         9 |    a |               0 |+----+-----------+------+-------+-----------

I need to get all leaves in this tree but stop digging in the sub-tree when the stop_digging is true.

In this sample, I need to get this expected Result

+----+-----------+------+-----------------+| id | parent_id | data | stop_digging    |+----+-----------+------+-----------------+|  1 |      NULL |    x |               0 ||  2 |         1 |    y |               0 ||  3 |         1 |    z |               1 ||  6 |         2 |    x |               0 ||  7 |         2 |    u |               1 ||  8 |         1 |    y |               1 |+----+-----------+------+-------+---------- 

the nodes that will be removed are:

+---- +-----------+------+-----------------+| id  | parent_id | data | stop_digging    |+---- +-----------+------+-----------------+|  4  |         3 |    g |               0 ||  5  |         3 |    l |               0 ||  9  |         7 |    y |               0 ||  10 |         9 |    a |               0 |+----+-----------+------+-------+-----------

CREATE TABLE Accounts (
    id int,
    parent_id int ,
    data  varchar(10),
    stop_digging int
);

INSERT INTO Accounts
    (`id`, `parent_id`, `data`, `stop_digging`)
VALUES
    (1, null,'x',0),
    (2,1,'y',0),
    (3,1,'z',1),
    (4,3,'g',0),
    (5,3,'l',0),
    (6,2,'x',0),
    (7,2,'u',1),
    (8,1,'y',1),
    (9,7,'y',0),
    (10,9,'a',0)
;

我可以获取所有节点，但我不知道有什么方法可以像我之前描述的那样只获取我需要的数据

select  id,
        data,
        parent_id, stop_digging
from    (select * from Accounts
         order by parent_id, id) Accounts,
        (select @pv := '1') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id);

Answer 1

我相信您要求的答案是错误的。必须包括 10，因为 9 在 stop_digging 列中有一个 0。

所以因为这个

SELECT a1.* FROM Accounts a1 
INNER JOIN (SELECT * FROM Accounts UNION SELECT NULL,0, 0, 0) a2 
ON a1.parent_id = a2.id OR (a1.parent_id IS NULL AND a2.id IS NULL) 
WHERE a2.stop_digging = 0;

给你以下回应

id  parent_id   data    stop_digging
1               x       0
2   1           y       0
3   1           z       1
6   2           x       0
7   2           u       1
8   1           y       1
10  9           a       0

UNION SELECT NULL,0, 0, 0

和

or (a1.parent_id IS NULL AND a2.id IS NULL)

需要零件，因为第一行不匹配，因为它没有以 stop_digging = 0 结束。

参见 dbfiddle 示例 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=46c7164f4b3b14e5e722ffcd822e8762