过滤SQL树查询
问题描述:
如果我有一个如下所示的树查询,我想筛选Name ='Son'并选择所有的父记录,所以结果应该产生前3行。我将如何构建我的查询?我读过我应该使用公用表表达式(CTE),但我是CTE上的新手。谁能帮我?谢谢。过滤SQL树查询
select 1 AS id, NULL AS parent, 'God' AS name
UNION
select 2 AS id, 1 AS parent, 'Father' AS name
UNION
select 3 AS id, 2 AS parent, 'Son' AS name
UNION
select 4 AS id, NULL AS parent, 'Godmother' AS name
UNION
select 5 AS id, 4 AS parent, 'Mother' AS name
答
听起来像是你可以存储在一个表中的树(或使用上面的SQL定义视图),然后如果你使用的是Oracle,您可以使用CONNECT BY功能来筛选记录。
答
这是你在找什么?
;with SomeCTE as
(
select *
from (
select 1 AS id, NULL AS parent, 'God' AS name
UNION
select 2 AS id, 1 AS parent, 'Father' AS name
UNION
select 3 AS id, 2 AS parent, 'Son' AS name
UNION
select 4 AS id, NULL AS parent, 'Godmother' AS name
UNION
select 5 AS id, 4 AS parent, 'Mother' AS name) as a
)
select *
from SomeCTE a
left join SomeCTE b
on a.parent = b.id
left join SomeCTE c
on b.parent = c.id
where a.name = 'son'
您使用的是什么数据库引擎和版本? – Lamak
http://*.com/questions/7651686/recursive-query-to-find-the-parent-record/ – Magnus