SQL：使用 OR 连接列

Question

我有两种类型的物品，假设它们是汽车和乘客。我有一张汽车桌和一张乘客桌。

我希望能够存储这些项目的关联，为此我使用了另一个表，称为“配对”。该架构如下所示：

Item1ID INT,
Item1Type INT,
Item2ID TEXT,
Item2Type INT,
PRIMARY KEY(Item1ID, Item2ID)

Item1Type 和 Item2Type 列保存一个枚举值，用于在查找时确定项目的类型。例如：如果我想将 Car1 与乘客 A 和乘客 B 相关联，该表可以有两个条目：

Item1ID,       Item1Type,     Item2ID,       Item2Type 
Car1           ItemTypes.Car  PassengerA     ItemTypes.Passenger
Car1           ItemTypes.Car  PassengerB     ItemTypes.Passenger

我遇到的问题是我还想将汽车与乘客相关联，因此 PassengerB 可以与汽车 C 和汽车 D 相关联，但是我已经有一个汽车 A 的条目和这两个额外的关联来将 PassengerB 映射到 CarC 和 CarD：

Item1ID,     Item1Type,           Item2ID,   Item2Type 
PassengerB   ItemTypes.Passenger  CarC       ItemTypes.Car
PassengerB   ItemTypes.Passenger  CarD       ItemTypes.Car

我需要做的是找到 Passenger B 的所有关联条目，所以在伪 SQL 中（使用SQlite），这将是：

SELECT * FROM Cars c 
          LEFT JOIN Pairings p ON 
          (c.CarID = p.Item1ID AND p.Item1Type = @CarPairingType) 
       OR (c.CarID = p.Item2ID AND p.Item2Type = @CarPairingType)

所以我需要在任一字段上进行内部联接，有点像开关，具体取决于类型是 0 还是 1。这可能吗，或者是否有更好的方法来解决这个问题？

Answer 1

为什么不将 Item1ID 和 Item2ID 更改为 carID 和 passengerID ？

那么您只需要表中的 2 列并像这样添加两行？

carID,       passengerID,
Car1           PassengerA
Car1           PassengerB

（将 PassengerB 映射到 CarC 和 CarD）：

CarC       PassengerB
CarD       PassengerB

Answer 2

也许UNION会“面向未来”？

SELECT * FROM Cars c 
INNER JOIN Pairings p ON c.CarID = p.Item1ID AND p.Item1Type = @CarPairingType
UNION
SELECT * FROM Cars c 
INNER JOIN Pairings p ON c.CarID = p.Item2ID AND p.Item2Type = @CarPairingType

Answer 3

从表面上看，OR 的正确拼写方式是 UNION：

SELECT *
  FROM Cars c 
  LEFT JOIN (SELECT p1.Item1ID AS CarID
               FROM Pairings AS p1
              WHERE p1.Item1Type = @CarPairingType
              UNION
             SELECT p2.Item2ID AS CarID
               FROM Pairings AS p2
              WHERE p2.Item2Type = @CarPairingType
            ) AS p ON c.CarID = p.CarID

不清楚您是否真正想要 LEFT JOIN；我希望只使用普通的（内部）联接。

我不相信这个设计是好的；按照 ypercube 的建议删除项目类型。敏捷编程有许多座右铭，其中之一是 YAGNI - You Ain't Gonna Need It。虽然关注未来是件好事，但我不确定您是否会从评论中暗示的扩展当前设计中受益。