mysql - 具有停止条件的嵌套集树的递归 SQL 查询

Question

我在表中使用嵌套集树结构。概念描述here .

示例数据如下:

+----+-----------+------+-------+-----------------+
| id | parent_id | left | right | stop_descending |
+----+-----------+------+-------+-----------------+
|  1 |      NULL |    1 |    10 |               0 |
|  2 |         1 |    2 |     3 |               0 |
|  3 |         1 |    4 |     9 |               1 |
|  4 |         3 |    5 |     6 |               0 |
|  5 |         3 |    7 |     8 |               0 |
+----+-----------+------+-------+-----------------+

获取整棵树非常简单:

SELECT t0.*
FROM nested_set AS t0
LEFT JOIN nested_set AS t1 ON t0.left BETWEEN t1.left AND t1.right
WHERE t1.parent_id IS NULL
ORDER BY t0.left;

但是，我想获取其父节点没有 stop_ descending 标志的所有节点。结果应包括节点 1、2、3。节点 4,5 应该被排除在外，因为它们的父节点有 stop_ descending 标志。如果节点 4 和 5 有 child ，则也应排除这些 child 。一旦 is_leaf 值等于 1，递归应该停止。

我尝试了很多不同的方法，但从未得到正确的结果。我正在 MariaDB 10.1.26 中运行查询。也许有更好的解决方案涉及更高版本的 CTE。

Answer 1

您执行另一个自连接以检查该叶子是否是具有 stop_decending = 1

的节点的一部分

SQL DEMO

SELECT t0.*, t1.*, t3.*
FROM nested_set AS t0
LEFT JOIN nested_set AS t1 
  ON t0.left BETWEEN t1.left AND t1.right

LEFT JOIN nested_set as t3
  ON t0.id BETWEEN t3.left AND t3.right
 AND t3.stop_descending  = 1

WHERE t1.parent_id IS NULL
  AND t3.id IS NULL
ORDER BY t0.left;

输出

| id | parent_id | left | right | stop_descending | id | parent_id | left | right | stop_descending |     id | parent_id |   left |  right | stop_descending |
|----|-----------|------|-------|-----------------|----|-----------|------|-------|-----------------|--------|-----------|--------|--------|-----------------|
|  1 |    (null) |    1 |    10 |               0 |  1 |    (null) |    1 |    10 |               0 | (null) |    (null) | (null) | (null) |          (null) |
|  2 |         1 |    2 |     3 |               0 |  1 |    (null) |    1 |    10 |               0 | (null) |    (null) | (null) | (null) |          (null) |
|  3 |         1 |    4 |     9 |               1 |  1 |    (null) |    1 |    10 |               0 | (null) |    (null) | (null) | (null) |          (null) |

对于调试注释过滤器 AND t3.id IS NULL