sql - 从多对多关系中选择完全外连接

Question

我正在尝试在 MSSQL 中做一些事情，我认为这在任何具有多对多关系的数据库中都是相当简单和常见的事情。然而，我似乎总是以非常复杂的选择查询结束，我似乎多次重复相同的条件以获得所需的输出。

场景是这样的。我有 2 个表(表 A 和 B)和一个交叉表，其中外键指向 A 和 B 的 ID 列。交叉表中只能有一对唯一的 A 和 B(我猜这 2 个外键组成了一个交叉表中的主键 ?!?)。三个表中的数据可能如下所示:

TABLE A          TABLE B            TABLE AB
ID     Type      ID     Type        AID    BID
--------------------------------------------------
R      Up         1      IN         R      3
S      DOWN       2      IN         T      3
T      UP         3      OUT        T      5
X      UP         4      OUT        Z      6
Y      DOWN       5      IN
Z      UP         6      OUT

现在假设我选择 A 中类型为 UP 的所有行和 B 中类型为 OUT 的所有行:

SELECT ID FROM A AS A1
WHERE Type = 'UP'
(Result: R, T, X, Z)

SELECT ID FROM B AS B1
WHERE Type = 'OUT'
(Result: 3, 4, 6)

我现在想要的是根据 AB 中列出的关系完全外部连接这 2 个子查询。因此，我希望 A1 和 B1 中的所有 ID 至少列出一次:

A.ID    B.ID
R       3
T       3
null    4
X       null
Z       6

我希望能够从这个结果集中看到:- A1 中的哪些行与 B1 中的任何行无关- B1 中的哪些行与 A1 中的任何行无关- A1 和 B1 行之间的关系

我尝试过一些方法，例如:

SELECT A1.ID, B1.ID
FROM (
    SELECT * FROM A
    WHERE Type = 'UP') AS A1

FULL OUTER JOIN AB ON
A1.ID = AB.AID

FULL OUTER JOIN (
    SELECT * FROM B
    WHERE Type = 'OUT') AS B1
ON AB.BID = B1.ID 

这是行不通的，因为 AB 中列出的一些关系是在 A1 中的行和不在 B1 中的行之间，或者在 B1 中但不在 A1 中的行之间。

换句话说——我似乎也被迫为 AB 表创建一个子查询:

SELECT A1.ID, B1.ID
FROM (
    SELECT * FROM A
    WHERE Type = 'UP') AS A1

FULL OUTER JOIN (
    SELECT * FROM AB AS AB1
    WHERE
      AID IN (SELECT ID FROM A WHERE type = 'UP') AND
      BID IN (SELECT ID FROM B WHERE type = 'OUT')
) AS AB1 ON
A1.ID = AB1.AID

FULL OUTER JOIN (
    SELECT * FROM B
    WHERE Type = 'OUT') AS B1
ON AB1.BID = B1.ID

对于看似简单的问题，这似乎是一个相当复杂的解决方案。特别是当您考虑到具有更多(复杂)条件的 A1 和 B1 子查询时 - 可能涉及连接到其他表(一对多)将需要在 AB1 子查询中重复相同的临时连接和条件。

我在想，一定有一种明显的方法可以重写上面的选择语句，以避免多次重复相同的条件。解决方案可能就在我面前，但我就是看不到。

如有任何帮助，我们将不胜感激。

Answer 1

我认为您可以在这种情况下使用 CTE，如下所示:

;WITH cte AS (
  SELECT A.ID AS AID, A.Type AS AType, B.ID AS BID, B.Type AS BType
  FROM A FULL OUTER JOIN AB ON A.ID = AB.AID
  FULL OUTER JOIN B ON B.ID = AB.BID)
SELECT AID, BID FROM CTE WHERE AType = 'UP' OR BType = 'OUT'

使用 CTE 的好处是它会被编译一次。然后你可以在 CTE 之外的 WHERE 子句中添加额外的条件

检查这个SQL Fiddle