想要根据上下级关系过滤数据， 请问有什么比较好的写法吗

Question

我有一个 目录表 和 一个关系表。
目录表里面存 所有的目录信息。CAT_ID, CAT_NAME，CAT_TYPE...
关系表里面存 一级目录和对应的子目录。CAT_LEAD, CAT_GROUPED。
我想要实现根据入参搜索符合要求的目录。
如果一级目录符合要求，结果里要排除它对应的子目录。
如果一级目录不符合要求，返回包括子目录在内的符合要求的目录。ex:
CAT_ID  CAT_NAME  CAT_TYPE
1 CAT1 A
2 CAT2 A
3 CAT3 B
4 CAT4 A
5 CAT5 ACAT_LEAD CAT_GROUPED
1 2
3 4如果我的搜索条件是CAT_TYPE = A
那么返回的结果是1，4，5.对目录表查询时的参数比较多，join了很多其他表。
请问有什么比较好的写法能实现这个功能，不增加太多执行时间吗？

Answer 1

with tab1 as (
select 1 id, 'c1' nam, 'a' typ from dual union all
select 2 id, 'c2' nam, 'a' typ from dual union all
select 3 id, 'c3' nam, 'b' typ from dual union all
select 4 id, 'c4' nam, 'a' typ from dual union all
select 5 id, 'c5' nam, 'a' typ from dual 
)
, tab2 as (
select 1 par, 2 children from dual union all
select 3, 4 from dual union all
select 3, 5 from dual
)
, tab3 as (
select*from tab1 t1, tab2 t2
 where 1 = 1
   and t1.id = t2.children(+)
)
select * 
  from tab3 t1
  where 1 = 1
    and t1.typ = 'a'
 start with t1.par is null
 connect by prior t1.id = t1.par
 and (level = 2 and prior t1.typ != 'a');除非你的关系是上下级多对多的关系，否则“关系表”是多余的，应该合并到“目录表”中。你现在只有一个层级可能感觉不出来，层级多了之后都要像tab3那样处理一下。

Answer 2

思路:
1、为目录表增加字段parent_id
2、找出每个parent_id的min(cat_id)  where cat_type=用户输入的值
3、min(cat_id)就是我们要的结果
sql:
select c.parent_id,min(c.cat_id) as cat_id from
(
select a.cat_id,a.cat_name,a.cat_type,
case when a.cat_id = b.cat_grouped then b.cat_lead
        else cat_id end as parent_id
 from 目录表 a,关系表 b 
) c 
where cat_type=用户输入的值
group by c.parent_id