首页 文章

SQL查询中的双向关系

提问于
浏览 次
7

我有一个用于跟踪零件的小型数据库 . 为了这个例子，表格如下所示：

PartID （PK），int
PartNumber ，Varchar（50），独特
Description ，Varchar（255）

我要求定义某些部分被分类为彼此相似 . 为此，我设置了第二个表，如下所示：

PartID ，（PK），int
SecondPartID ，（PK），int
ReasonForSimilarity ，Varchar（255）

然后在两个表之间 Build 了多对多关系 .

当我需要报告被认为相似的部分时会出现问题，因为这种关系是双向的 . 如果部分XYZ123类似于ABC678，那么ABC678被认为与XYZ123相似 . 因此，如果我想列出与给定部分类似的所有部分，我需要确保在两个方向上设置关系（这是错误的，因为数据是重复的）或者需要有2个查询在两个方向上查看表 . 这些解决方案都不适合我 .

那么，如何解决这个问题呢？这可以单独用SQL解决，还是我的设计需要改变以适应业务需求？

考虑以下部分XYZ123，ABC123，ABC234，ABC345，ABC456和EFG456，它们已输入上面输入的现有结构中 . 您最终可能会看到这样的数据（省略此时无关的原因字段）：

PartID ， SecondPartID
XYZ123，ABC123
XYZ123，ABC234
XYZ123，ABC345
XYZ123，ABC456
EFG456，XYZ123

我的用户想知道"Which parts are similar to XYZ123" . 这可以使用如下查询来完成：

SELECT SecondPartID
FROM tblRelatedParts
WHERE PartID = 'XYZ123'

但问题是它不会挑选出与XYZ123相关的部分EFG456，尽管这些部件已经反过来了 . 根据用户当前正在使用哪个部分以及部件之间的关系将始终是双向的，这是可行的 .

我对此的问题是，我现在需要检查当用户在两个部分之间 Build 关系时，它在另一个方向上尚不存在 .

@Goran

我已经使用您的建议做了一些初步测试，这就是我计划使用您的建议来解决问题的方法 .

上面列出的数据输入到新表中（请注意，我已将partID更改为部件号以使示例更清晰;但我的问题的语义没有改变）

该表如下所示：

RelationshipID ， PartNumber
1，XYZ123
1，ABC123
2，XYZ123
2，ABC234
3，XYZ123
3，ABC345
4，XYZ123
4，ABC456
5，EFG456
5，XYZ123

然后，我可以使用如下查询检索类似部件的列表：

SELECT PartNumber
FROM tblPartRelationships
WHERE RelationshipID ANY (SELECT RelationshipID
                          FROM tblPartRelationships
                          WHERE PartNumber = 'XYZ123')

我会进行更多的测试，如果有效，我会反馈并接受答案 .

sql database-design relational-database

4 回答

  • 2

    我通过 Build 关系表来处理这个问题 .

    零件表：

    PartID （PK），int

    PartNumber ，Varchar（50），独特

    Description ，Varchar（255）

    PartRelationship表：

    RelationshipId （FK），int

    PartID （FK），int

    关系表：

    RelationshipId （PK），int

    现在类似的部分只是添加到关系表：

    RelationshipId，PartId

    1,1

    1,2

    每当您添加relationshipId = 1的其他部分时，它被认为类似于relationshipId = 1的任何部分 .

    添加关系的可能API解决方案：

    • 为每个相似部件列表创建新关系 . 让客户端在需要时加载，更改和更新整个列表 .

    • 检索类似对象的关系 . 按某些条件筛选列表，以便只保留一个或让客户从现有关系中进行选择 . 根据需要创建，删除PartRelationship记录 .

    • 从关系表中检索关系列表 . 让客户指定部件和关系 . 根据需要创建，删除PartRelationship记录 .

    回复于
  • 7

    添加 CHECK 约束，例如

    CHECK (PartID < SecondPartID);
    回复于
  • 0

    我知道这是旧的，但为什么不用原始架构进行此查询？减少表格和行数 .

    SELECT SecondPartID
FROM tblRelatedParts
WHERE PartID = 'XYZ123' 
UNION
SELECT PartID
FROM tblRelatedParts
WHERE SecondPartID = 'XYZ123'

    我正在处理类似的问题并查看这两种方法，并想知道为什么你认为关系表的架构更好 . 从某种意义上说，原始问题似乎仍然存在，你仍然需要从两个方向管理它们之间的关系 .

    回复于
  • 0

    如何为每个相似性设置两行 . 例如，如果对象A，B与关系表中的对象类似

    A B
B A

    我知道你会将你的关系数据加倍，但它们是整数，所以它不会过度杀死你的数据库 . 相反，你有一些收获：

    • 你不会使用工会 . 在任何dbms中联盟都被杀死了 . 特别是当你有订购或分组时

    • 你可以实现更具体的关系：a与b有关，但b与a无关 . 例如约翰可以取代戴夫，但戴夫不能取代约翰 .

    回复于

相关问题