在Oracle中,如何从按子代值过滤的分层查询中选择行?

尝试反转 CONNECT BY 条件并从(即。, START WITH )您所知道的:

SELECT DISTINCT strval, parent_id, id
FROM stores
where subtype = 'building'
CONNECT BY id = prior parent_id
START WITH subtype = 'object' and subval = 'macbook';

在评论中,您问到如果起始值不在同一级别怎么办?

在这种情况下,恐怕你必须查看每栋建筑的整棵树,然后进行过滤。

insert into stores values (8, 4, 'Year','edition','2015');

然后,该查询给出了答案:

WITH whole_tree AS
       (SELECT strval,
               parent_id,
               id,
               CONNECT_BY_ROOT(strval) building,
               SYS_CONNECT_BY_PATH (subtype || ':' || subval, ',') PATH
        FROM   stores
        CONNECT BY PRIOR id = parent_id
        START WITH subtype = 'building')
SELECT distinct building
FROM   whole_tree
WHERE  PATH LIKE '%object:macbook%edition:2015%';

select *
from
(
select *
from stores
where subtype = 'object' 
and strval = 'Macbook'
) macs
join
(
select *
from stores 
where subtype = 'building'
) bld
on bld.id = macs.parent_id