分层结构的 SQL 查询

Question

我一直在尝试为分层结构编写查询。我需要知道除了下面提到的方法之外，是否有更好的方法来编写此查询。病理顺序的结构如下： 医院 -> 实验室 -> 部分 -> 小节 -> 工作台/仪器

有单独的表"资源"，它将所有这些列保存为父子关系(child_resource，parent_resource)以及资源类型。 例如

表资源 ：

parent_resource    child_resource    resource        resource_type
DE Hospital        DE Section        DE Lab          Lab
DE Lab             DF Sub Section    DF Section      Section
DE Section         DE bench          DF SubSection   Bench
DE Section         DF bench          DF SubSection   Bench
DE Section         DG bench          DF SubSection   Bench

另一个表订单包含这两个表的所有链接。

表顺序 ：

Order_id         resource_Type    Resource  
12345            SubSection       DF SubSection
23456            bench            DG bench
34567            Section          DE Section

我希望结果为

Order_id   resource         Hospital       Lab      Section      Subsection      Bench      
12345      DF SubSection    DE Hospital    DE Lab   DE Section   DF SubSection   -
23456      DG bench         DE Hospital    DE Lab   DE Section   DF SubSection   DG bench
34567      DE Section       DE Hospital    DE Lab   DE Section     -             -

为了实现上述结果，我可以根据resource_type有多个左联接，(如果资源类型是子部分，则它不会捕获工作台信息)，(如果资源类型是部分，则它应该只捕获医院，实验室，部分，即使信息存在，也不应捕获其他内容)。

左一加盟 ：

left outer join (select *
from resource rg
join resource rg_section on rg.child_resource = 
rg_section.parent_resource
and rg_section.active_ind=1
join resource_group rg_subsection on rg_subsection.parent_resource = rg_section.child_resource
and rg_subsection.active_ind=1
where rg.active_ind=1
) sr_rs on 
order.resource in (orders.resource_type(subsection))

左接第二个连接：

left outer join (select
from resource rg
join resource rg_section on rg.child_resource = rg_section.parent_resource
and rg_section.active_ind=1
join resource rg_subsection on rg_subsection.parent_resource = 
rg_section.child_resource
and rg_subsection.active_ind=1
join resource rg_bench on rg_bench.parent_resource = 
rg_subsection.child_resource
and rg_bench.active_ind=1
join resource sr on sr.service_resource_cd = rg_bench.child_resource
and sr.active_ind=1
where rg.active_ind=1
) sr_rs on 
order.resource in (orders.resource_type(bench))

Answer 1

这是我的尝试。我首先构建了层次结构，然后将订单加入其中，并有条件地对适当的列进行分组。数据的组织方式使任务复杂化，但最后我得到了预期的结果：

with hierarchy as (
select r.*, level, connect_by_root(child_resource) root
from resources r
connect by prior resource_ = child_resource 
or resource_ = prior parent_resource) 
select order_id, root, 
max(case h.resource_type when 'Lab' then h.parent_resource end) hospital,
max(case h.resource_type when 'Lab' then h.resource_ end) lab,
max(case h.resource_type when 'Lab' then h.child_resource end) section,
max(case h.resource_type when 'Section' then h.child_resource end) subsection,
max(case h.resource_type when 'Bench' then h.child_resource end) bench
from orders o join hierarchy h on h.root = o.resource_
group by order_id, root order by order_id

^{DBfiddle 演示}

请检查并测试。我希望它会有所帮助。