从包含相互关系的表中提取唯一对 [英] Extract Unique Pairs from Table Containing Reciprocal Relationships

问题描述

考虑一个 SQL Server 2005 数据库，其中包含大量关于许多人的数据，其中很大一部分已婚.为了跟踪人与人之间的关系，存在一个关系表，用于将一个人的 ID 链接到另一个人的 ID.每个关系记录上的 关系类型 用于指示所讨论的两个人之间存在的关系类型(废话):嫁给"，是儿子的"，是女儿的"，等

Consider a SQL Server 2005 database with a bunch of data on a lot of people, a large chunk of whom are married. To track relationships among people, a Relationship table exists that serves to link the ID of one person to the ID of another. A Relationship Type on each Relationship record serves to indicates the type of relationship exists between the two people in question (duh): "is married to", "is son of", "is daughter of", etc.

对于已婚人士，表中有两条关系记录——一条显示 A 与 B 结婚，另一条记录显示 B 与 A 结婚.对于婚姻，虽然数据库中有一些未返回的记录，但没有两个记录会被认为是错误的.(这是一个继承的设计，我没有改变这种方法的自由.)

For married people, there are TWO relationship records in the table--one showing Person-A is married to Person-B and another record showing that Person-B is married to Person-A. For a marriage, it would be considered an error to not have both records, though there are some un-reciprocated records in the DB. (This is an inherited design and I do not have the freedom to change this approach.)

RelID  Person1  Person2  RelationshipType
-----------------------------------------
1      1001     1010     Married           //Reciprocated by RelID 4
2      1002     1011     Married           //Reciprocated by RelID 5
3      1003     1012     Married           //Reciprocated by RelID 6
4      1010     1001     Married           //Reciprocated by RelID 1
5      1011     1002     Married           //Reciprocated by RelID 2
6      1012     1003     Married           //Reciprocated by RelID 3
7      1004     1013     Married           //Not properly reciprocated

我需要做的是从表中提取独特的已婚夫妇.

What I need to do is extract unique married couples from the table.

我创建了一个名为 GetOrderedPair(val1,val2) 的函数，它接受 2 个个人 ID，并按顺序返回两个值的逗号分隔字符串值.这样，无论我是否得到，它总是返回相同的字符串值，参考上面的示例数据，Person1 和 Person2 来自 RelID 为 1 或 RelID 为 4 的记录(一对往复记录).

I created a function called GetOrderedPair(val1,val2) which takes 2 Person IDs and returns a comma separated string value of both values in sequential order. This way, it always returns the same string value regardless of whether I get, referring to above example data, Person1 and Person2 from record with RelID of 1 or RelID of 4 (a reciprocating pair of records).

我不禁想到有一种更好的方法可以躲避我.使用这个功能是最好的方法吗?您还可以推荐什么?

I can't help but think that there is a better way that eludes me. Is the use of this function the best approach? What else might you recommend?

谢谢，当然，提前.:-)

Thanks, of course, in advance. :-)

推荐答案

既然你已经封装了 GetOrderPair，我不知道它背后的逻辑，但我至少会给你我所做的:

Since you've encapsulated GetOrderPair, I have no idea the logic behind it, but I'll at least give you what I do:

select distinct
    case when Person1 > Person2 then Person2 else Person1 end as Person1,
    case when Person1 > Person2 then Person1 else Person2 end as Person2
from
    couples
where
    relationshiptype = 'Married'

也许有人比我有更好的方法.

Maybe somebody's got a better way of doing this than I do.

这篇关于从包含相互关系的表中提取唯一对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！