sql - 多个多对多双向自内连接，无需重复整个查询

Question

我有一个数据模型，这样项目可以与同一个表中的其他项目建立多对多关系，使用第二个表来定义关系。让我们调用主表 items，以 item_id 和关系表 item_assoc 为 item_id 和 列other_item_id 和 assoc_type。通常，您可能会使用 union 来获取可能在 item_assoc 表中沿任一方向定义的关系，但最终会重复同一查询的其他部分只是为了确保选择在任一方向定义的关联。

假设您正在尝试将一个类似于以下内容的相当复杂的查询放在一起，您希望在其中查找具有相关项目的项目列表，这些项目可能具有关联的取消项目，但选择那些没有取消项目的项目:

select 
  orig.*
from items as orig

join item_assoc as orig2related
  on orig.item_id = orig2related.item_id
join items as related
  on orig2related.other_item_id = related.item_id
 and orig2related.assoc_type = 'Related'

left join item_assoc as related2cancel
  on related.item_id = related2cancel.item_id
left join items as cancel
  on related2cancel.other_item_id = cancel.item_id
 and related2cancel.assoc_type = 'Cancellation'

where cancel.item_id is null

这个查询显然只挑选关系在一个方向上定义的项目。对于不太复杂的查询，我可能会通过在底部为反向关系的每个排列添加一个 union 来解决这个问题，但我认为这会使查询不必要地冗长且难以理解。

有没有一种方法可以在不重复查询的其他部分的情况下定义每个关系的两个方向？

Answer 1

item_assoc 中的 UNION 可以提供帮助。假设您有一个没有 WITH 子句的数据库，您将必须定义一个 View

CREATE VIEW bidirec_item_assoc AS
(
SELECT item_id, other_item_id, assoc_type, 1 as direction FROM item_assoc
UNION
SELECT other_item_id, item_id, assoc_type, 2 as direction FROM item_assoc
)

您现在可以在之前使用 items_assoc 的查询中使用 bidirec_item_assoc。

已编辑:当然，您可以为方向和关系类型添加列