我有一张层次 struct 的表格
select * from product_categories;
id | parent_id | item | rule
----+-----------+--------------+---------------
1 | | ecomm |
2 | 1 | grocceries |
3 | 1 | electronics |
5 | 3 | TV |
6 | 4 | touch_screen | Rules applied
7 | 4 | qwerty |
8 | 6 | iphone |
4 | 3 | mobile | mobile rules
我想从iPhone遍历,一旦我遇到规则不为空的行,我就想 Select 该行并仅在此时和那里完成递归, 我正在使用这个查询,
WITH RECURSIVE items AS (
SELECT id, item, parent_id, rule
FROM product_categories
WHERE item = 'iphone'
UNION ALL
SELECT p.id, p.item, p.parent_id, p.rule
FROM product_categories p
JOIN items ON p.id = items.parent_id
WHERE p.rule is not NULL
)
SELECT * FROM items ;
给出结果,
id | item | parent_id | rule
----+--------------+-----------+---------------
8 | iphone | 6 |
6 | touch_screen | 4 | Rules applied
4 | mobile | 3 | mobile rules
这里我想要的是,一旦找到规则列不为空的行,它就应该返回该行并完成递归, 在上面的例子中,它应该返回第二行和项‘Touch_Screen’, 但它也在打印第三行.
如何修改此查询以实现此目标