多表之间的SQL映射

Question

这是一个SQL设计问题。首先，设置。我有三个表：

1. A，它是根据对链接服务器的查询自动填充的。该表中的数据不能更改；
2. B，只有十几行，包含 As 集合的名称；
3. AtoB，它是映射表，通过它将 As 组织成命名集合，两列上都有外键；

例如，A 包含：

1. 长颈鹿
2. 猫头鹰
3. 老虎

且 B 包含：

1. 西雅图动物园
2. 圣何塞动物园

且 AtoB 包含：

1 ,1（西雅图的长颈鹿）
2,1（西雅图的猫头鹰）
3,1（西雅图的老虎）
2,2（圣何塞的猫头鹰）

现在，问题是：

我被要求在其中一些集合中包含 A 中找不到的项目。因此，我创建一个表 C，其标识和名称列与 A 相同，并填充它。与前面的示例保持一致，假设 C 包含：

1. Dragon

问题是，如何在 AtoB 中包含 C 中的项目？如果我需要在西雅图动物园养一条龙怎么办？

我天真的第一直觉是创建一个包含 A 和 C 的并集的视图 V，并将 AtoB 修改为 VtoB。这就是我的天真得到回报的地方：人们无法为视图创建外键。

我怀疑存在一种标准、正确的方法将一个或多个 A 或 C 与 B 相关联。

Answer 1

为了扩展 Arthur Thomas 的解决方案，这里有一个在子选择中没有 WHERE 的 union ，以便您可以创建通用视图：

SELECT A.Name as Animal, B.Name as Zoo FROM A, AtoB, B
    WHERE AtoB.A_ID = A.ID && B.ID = AtoB.B_ID 
UNION
SELECT C.Name as Animal, B.Name as Zoo FROM C, CtoB, B
    WHERE CtoB.C_ID = C.ID && B.ID = CtoB.B_ID

然后，您可以执行如下查询：

SELECT Animal FROM zoo_animals WHERE Zoo="Seattle Zoo"

Answer 2

如果您不能将龙放入 A 中，那么您将需要创建另一个表和另一个链接表。问题是创建一个需要存储的唯一数据集（另一个表），该数据集不能与 A 相同。由于它不是同一集，因此您不能再使用具有外部数据的链接表（AtoB）确保链接是来自集合 A 的引用的键。因此，您可以创建如下表：

imaginary_creatures

• id
• name

imaginary_creatures_to_b

• imaginary_creatures_id （链接到 imaginary_creatures 表）
• b_id （链接到动物园表）

稍后，当您想要获取 a 中的所有生物时动物园你可以做一个联盟

SELECT A.Name FROM A where A.ID IN 
   (SELECT AB.A_ID FROM AtoB AB WHERE B_ID = 
      (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))
UNION
SELECT i.name FROM imaginary_creatures i i.id IN 
   (SELECT ic.imaginary_creatures_id FROM imaginary_creatures_to_c ic 
    WHERE ic.b_id = (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))

可能有更好的写法，但它应该适合你的目的。

Answer 3

Arthur Thomas 有一个很好的解决方案，另一个可能的解决方案是在链接表中添加一列，指示它与哪个表（A 或 C）相关。然后通过触发器而不是外键来加强关系。但事实上，亚瑟的解决方案是做这类事情的首选方法。

Answer 4

你想要做的是将 Dragon 放入 A 中，如果你想选择 A 中的所有记录，无论它们在 AtoB 中是否有匹配的记录，请执行 LEFT OUTER JOIN。像这样的事情：

SELECT * FROM A
LEFT OUTER JOIN AtoB
ON A.id = AtoB.A_ID

编辑：只有当您可以将新记录添加到 A 时，这才有效。我错过了您不能这样做的事实。我认为亚瑟托马斯的解决方案就是你想要的。

Answer 5

1. 截断表 dept_details
2. 显示表 emp_details 的结构
3. 将 emp_name 的第一个字母转换为大写。