sql - 树结构查询中逻辑或的完全外部连接的替代方法

Question

我希望标题足够清楚。我一直在为保存在数据库中的树结构实现逻辑与/或，使用一个简单的节点和一个父子关联表。示例树具有如下结构:

示例树结构查询如下:

查询模式中的双线表示 A 有一个类型为 B 的子节点(位于其子节点下方的某处)或 C。我已经实现了 A -> HASCHILD -> C -> HASCHILD -> E 与内部连接，和 A -> HASCHILD -> B -> HASCHILD -> E 是这样实现的。诀窍是在 A 上连接这两个分支。由于这是一个 OR 操作，B 分支或 C 分支可能不存在。我能想到的唯一方法是使用以 A 的 node_id 为键的两个分支的完全外部连接。为了避免细节，让我从我的 SQL 查询中给出这个简化的片段:

    WITH A as (....),
    B as (....),
    C as (....),
    ......

SELECT * 
      from
          A
          INNER JOIN A_CONTAINS_B ON  A.NODE_ID = A_CONTAINS_B.parent
          INNER JOIN B ON A_CONTAINS_B.children @> ARRAY[B.NODE_ID]
          INNER JOIN .....

          full OUTER JOIN -- THIS IS WHERE TWO As ARE JOINED
          (select
          A2.NODE_ID AS A2_NODE_ID
          from
          A2
          INNER JOIN A_CONTAINS_C ON A2.NODE_ID = C_CONTAINS_C.parent
          INNER JOIN C ON A_CONTAINS_C.children @> ARRAY[C.NODE_ID]
          INNER JOIN ....) 
          as other_branch
          ON other_branch.A2_NODE_ID = A.NODE_ID

此查询链接两个 As，它们实际上使用 node_id 表示相同的 A，如果 B 或 C 不存在，则不会中断。结果集当然有重复项，但我可以接受。但是，我想不出在这种情况下实现 OR 的另一种方法。 AND 很简单，它们是内部联接，但左外部联接是唯一让我连接 As 的方法。两个分支都带有虚拟列的 UNION ALL 不是一个选项，因为在那种情况下我无法连接 As。

除了我在这里所做的，你还有其他选择吗？

更新

TokenMacGuy 的建议为我提供了一条比我现在所拥有的路线更清晰的路线。我应该记得 UNION。使用他建议的第一种方法，我可以应用查询模式分解，这将是使用逻辑运算符分解查询的一致方法。以下是我将要执行的操作的可视化表示，以防万一它可以帮助其他人可视化该过程:

这帮助我做了很多好事，包括创建一个很好的结果集，其中查询模式组件链接到结果。我故意避免表或其他上下文的细节，因为我的问题是关于如何连接查询结果。我如何处理数据库中的层次结构是我想避免的另一个话题。我会在评论中添加更多细节。这基本上是一个带有层次结构表的 EAV 表。以防万一有人想看到它，这是我在遵循 TokenMacGuy 的建议后运行的查询，没有任何简化:

WITH
    COMPOSITION1 as (select comp1.* from temp_eav_table_global as comp1
                      WHERE
                      comp1.actualrmtypename = 'COMPOSITION'),
    composition_contains_observation as (select * from parent_child_arr_based),
    OBSERVATION as (select obs.* from temp_eav_table_global as obs
                        WHERE
                        obs.actualrmtypename = 'OBSERVATION'),
    observation_cnt_element as (select * from parent_child_arr_based),
    OBS_ELM as (select obs_elm.* from temp_eav_table_global as obs_elm
                        WHERE
                        obs_elm.actualrmtypename= 'ELEMENT'),

     COMPOSITION2 as (select comp_node_tbl2.* from temp_eav_table_global as comp_node_tbl2
                          where
                          comp_node_tbl2.actualrmtypename = 'COMPOSITION'),
    composition_contains_evaluation as (select * from parent_child_arr_based),
    EVALUATION as (select eva_node_tbl.* from temp_eav_table_global as eva_node_tbl
                            where
                            eva_node_tbl.actualrmtypename = 'EVALUATION'),
    eval_contains_element as (select * from parent_child_arr_based),
    ELEMENT as (select el_node_tbl.* from temp_eav_table_global as el_node_tbl
                            where
                            el_node_tbl.actualrmtypename = 'ELEMENT')



select
                      'branch1' as branchid,
                      COMPOSITION1.featuremappingid as comprootid,
                      OBSERVATION.featuremappingid as obs_ftid,
                      OBSERVATION.actualrmtypename as obs_tn,
                      null as ev_ftid,
                      null as ev_tn,
                      OBS_ELM.featuremappingid as obs_elm_fid,
                      OBS_ELm.actualrmtypename as obs_elm_tn,
                      null as ev_el_ftid,
                      null as ev_el_tn

                      from
                      COMPOSITION1
                      INNER JOIN composition_contains_observation ON COMPOSITION1.featuremappingid = composition_contains_observation.parent
                      INNER JOIN OBSERVATION ON composition_contains_observation.children @> ARRAY[OBSERVATION.featuremappingid]
                      INNER JOIN observation_cnt_element on observation_cnt_element.parent = OBSERVATION.featuremappingid
                      INNER JOIN OBS_ELM ON observation_cnt_element.children @> ARRAY[obs_elm.featuremappingid]

UNION

SELECT                  
                        'branch2' as branchid,
                        COMPOSITION2.featuremappingid as comprootid,
                        null as obs_ftid,
                        null as obs_tn,
                        EVALUATION.featuremappingid as ev_ftid,
                        EVALUATION.actualrmtypename as ev_tn,
                        null as obs_elm_fid,
                        null as obs_elm_tn,
                        ELEMENT.featuremappingid as ev_el_ftid,
                        ELEMENT.actualrmtypename as ev_el_tn                       
                  from
                      COMPOSITION2
                      INNER JOIN composition_contains_evaluation ON  COMPOSITION2.featuremappingid = composition_contains_evaluation.parent
                      INNER JOIN EVALUATION ON composition_contains_evaluation.children @> ARRAY[EVALUATION.featuremappingid]
                      INNER JOIN eval_contains_element ON EVALUATION.featuremappingid = eval_contains_element.parent
                      INNER JOIN ELEMENT on eval_contains_element.children @> ARRAY[ELEMENT.featuremappingid]

Answer 1

等价于∨的关系是⋃。您可以使用 union 将 a JOIN b JOIN e 与 a JOIN c JOIN e 组合，或者只使用 b 和 c 的联合并加入在生成的组合关系上，类似于 a JOIN (b UNION c) JOIN e

更完整:

SELECT *
FROM a
JOIN (
    SELECT
        'B' source_relation,
        parent,
        b.child,
        b_thing row_from_b,
        NULL row_from_c
    FROM a_contains_b JOIN b ON a_contains_b.child = b.node_id

    UNION
    SELECT
        'C',
        parent
        c.child,
        NULL,
        c_thing
    FROM a_contains_c JOIN c ON a_contains_c.child = c.node_id
) a_c ON A.NODE_ID = a_e.parent
JOIN e ON a_c.child = e.node_id;