如何联接这些表以获得包含所有表的完整结果集？

Question

如何联接这些表以获得包含所有表的完整结果集？

sqlsql-serversql-server-2017

3

我有实体表A，物品表B和实体-物品表C，以及它们关联的配置值。我想编写一个视图，返回每个实体+物品组合的行，并在存在表C的情况下使用其数据。此外，如果表C中有不在表B中的物品，我也希望将其包含在内。

例如：

表A 实体1 实体2

表B 项X 项Y 项Z

表C 实体1 项X True 实体1 项Y False 实体2 项X False 实体2 项J False 所需结果表：实体1 项X True 实体1 项Y False 实体1 项Z Null 实体2 项X False 实体2 项Y Null 实体2 项Z Null 实体2 项J False 对于这个问题，您可以尝试使用以下SQL查询语句：

SELECT A.Entity, B.Item, C.Value FROM A CROSS JOIN B LEFT JOIN C ON A.Entity=C.Entity AND B.Item=C.Item

这会返回所有实体和物品的组合，包括那些在表C中不存在的物品，空值表示这些物品没有配置值。

- Rachel

2

您的声望太高了，不能像这样发布示例数据。请使用适当的DDL+DML编辑您的问题。 - Zohar Peled

抱歉，我不知道你的意思。仅仅因为我在SO的某个领域有声望并不意味着我了解另一个领域 =/ 这只是意味着我非常活跃（或曾经很活跃）。 - Rachel

DDL => 创建表语句(s)。 DML => 插入语句(s)。 - Zohar Peled

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

你的 cross join/left join 是正确的方法：最初的回答

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     Items i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;

然而，这假设ItemId已经正确定义为外键关系。看起来你有无效的ItemId。你是可以修复它的：

最初的回答：假设ItemId已经正确定义为外键关系。如果你有无效的ItemId，你可以进行修复。

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     (SELECT i.ItemId
      FROM Items i
      UNION  -- on purpose to remove duplicates
      SELECT ei.ItemId
      FROM EntityItems ei
     ) i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;

然而，我强烈建议您修复数据（即向项目表中添加J）并添加以下内容： "最初的回答"

alter table entityitems add constraint fk_entityitems_entityid
    foreign key (entityid) references entities(entityid);

alter table entityitems add constraint fk_entityitems_itemid
    foreign key (itemid) references entities(itemid);

这将有助于确保数据完整性（在您修复数据之后）。

编辑：

啊，您不想在所有实体上添加附加项ID。如果是这样：

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     Items i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;
UNION ALL
SELECT ei.EntityId, ei.ItemId, ei.value
FROM EntityItems ei
WHERE NOT EXISTS (SELECT 1 FROM Items i WHERE i.ItemId = ei.ItemId);