SQL查询中的双向关系 [英] Two way relationships in SQL queries

问题描述

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

PartID （PK），int

部分号，Varchar（50），唯一

描述，Varchar（255）

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

PartID ，（PK），int

SecondPartID ，（PK），int

ReasonForSimilarity ，Varchar（255）

然后，在两个表之间建立了多对多的关系。

当我需要报告被认为相似的部分时，问题出在关系是IE的两种方式如果XYZ123部分类似于ABC678 ，那么ABC678 被认为与XYZ123 类似。所以如果我想列出与给定部分相似的所有部分，我需要确保关系是双向设置的（这是因为数据被重复的原因），或者需要有两个查询来查看两个方向的表格。这两种解决方案对我来说都不是很合适。

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

考虑以下部分：XYZ123，ABC123，ABC234，ABC345，ABC456&已输入上述输入的现有结构的EFG456。你最终可以看出这样的数据（省略原因字段在这一点上无关紧要）：

PartID ， SecondPartID

XYZ123，ABC123

XYZ123，ABC234

XYZ123，ABC345

XYZ123，ABC456

EFG456，XYZ123

我的用户想知道哪些部分与XYZ123类似。这可以使用如下所示的查询：

  SELECT SecondPartID 
 FROM tblRelatedParts 
 WHERE PartID =' XYZ123'
  

尽管如此，它的问题是不会选择与XYZ123相关的部分EFG456，尽管事实上，这些部分已经进入了另一个方面。根据用户当前正在使用的哪一部分，这可能会发生这种情况，并且这些部分之间的关​​系将始终是双向的。

我有这个问题的是，我现在需要检查一下，当用户设置两个部分之间的关​​系时，它不是存在于另一个方向。

@Goran

我已经使用您的建议进行了一些初步测试，是如何计划使用您的建议来解决问题。

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

表格如下所示：

RelationshipID ， PartNumber

1，XYZ123

1，ABC123

2，XYZ123 < br>
2，ABC234

3，XYZ123

3，ABC345

4，XYZ123

4，ABC456 < br>
5，EFG456

5，XYZ123

然后，我可以使用查询来检索类似的零件列表这是：

  SELECT PartNumber 
 FROM tblPartRelationships 
 WHERE RelationshipID ANY（SELECT RelationshipID 
 FROM tblPartRelationships 
 WHERE PartNumber ='XYZ123'）
  

我将进行一些更多的测试，如果这个工作我会反馈并接受答案。

解决方案

我已经通过设置关系表来处理这个问题。

零件表：

PartID （PK），int

PartNumber ，Varchar（50），Unique

描述 Varchar（255）

PartRelationship表：

RelationshipId （FK），int

PartID （FK），int

关系表：

RelationshipId （PK），int

现在，相似的部分只是添加到关系表中： / p>

RelationshipId，PartId

1,1

1,2

每当您添加关系= 1的其他部分时，它被认为与relationshipId = 1的任何部分相似。

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

• 为每个类似的部件列表创建新的关系。允许客户端在需要时加载，更改和更新整个列表。


• 检索类似对象的关系。按照某些标准过滤列表，以便只有一个保留或允许客户端从现有关系中进行选择。根据需要创建，删除PartRelationship记录。


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

I have a small database that is used to track parts. for the sake of this example the table looks like this:

PartID (PK), int
PartNumber, Varchar(50), Unique
Description, Varchar(255)

I have a requirement to define that certain parts are classified as similar to each other. To do this I have setup a second table that looks like this:

PartID, (PK), int
SecondPartID, (PK), int
ReasonForSimilarity, Varchar(255)

Then a many-to-many relationship has been setup between the two tables.

The problem comes when I need to report on the parts that are considered similar because the relationship is two way I.E. if part XYZ123 is similar to ABC678 then ABC678 is considered to be similar to XYZ123. So if I wanted to list all parts that are similar to a given part I either need to ensure the relationship is setup in both directions (which is bad because data is duplicated) or need to have 2 queries that look at the table in both directions. Neither of these solutions feels right to me.

So, how should this problem be approached? Can this be solved with SQL alone or does my design need to change to accommodate the business requirement?

Consider the following parts XYZ123, ABC123, ABC234, ABC345, ABC456 & EFG456 which have been entered into the existing structure entered above. You could end up with data that looks like this (omitting the reason field which is irrelevant at this point):

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

My user wants to know "Which parts are similar to XYZ123". This could be done using a query like so:

SELECT SecondPartID
FROM tblRelatedParts
WHERE PartID = 'XYZ123'

The problem with this though is it will not pick out part EFG456 which is related to XYZ123 despite the fact that the parts have been entered the other way round. It is feasible that this could happen depending on which part the user is currently working with and the relationship between the parts will always be two-way.

The problem I have with this though is that I now need to check that when a user sets up a relationship between two parts it does not already exist in the other direction.

@Goran

I have done some initial tests using your suggestion and this is how I plan to approach the problem using your suggestion.

The data listed above is entered into the new table (Note that I have changed the partID to part number to make the example clearer; the semantics of my problem haven't changed though)

The table would look like this:

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

I can then retrieve a list of similar parts using a query like this:

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

I'll carry out some more tests and if this works I'll feedback and accept the answer.

解决方案

I've dealt with this issue by setting up a relationship table.

Part table:

PartID (PK), int

PartNumber, Varchar(50), Unique

Description, Varchar(255)

PartRelationship table:

RelationshipId (FK), int

PartID (FK), int

Relationship table:

RelationshipId (PK), int

Now similar parts simply get added to Relationship table:

RelationshipId, PartId

1,1

1,2

Whenever you add another part with relationshipId = 1 it is considered similar to any part with relationshipId = 1.

Possible API solutions for adding relationships:

• Create new relationship for each list of similar parts. Let client load, change and update the entire list whenever needed.
• Retrieve relationship(s) for a similar object. Filter the list by some criteria so that only one remains or let client choose from existing relationships. Create, remove PartRelationship record as needed.
• Retrieve list of relationships from Relationship table. Let client specify parts and relationships. Create, remove PartRelationship records as needed.

这篇关于SQL查询中的双向关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！