T-Sql查询 - 获得2列的唯一行

I have a set of data, with columns x and y. This set contains rows where, for any 2 given values, A and B, there is a row with A and B in columns x and y respectivly and there will be a second row with B and A in columns x and y respectivly.

我有一组数据，列x和y。该集包含行，其中对于任何给定的值A和B，在x和y列中分别有A和B的行，并且在x和y列中将分别有第二行B和A.

E.g

例如

        **Column X**            **Column Y**
Row 1        A                       B
Row 2        B                       A

There are multiple pairs of data in this set that follow this rule.
此集合中有多对数据符合此规则。
For every row with A, B in Columns X and Y, there will always be a row with B, A in X and Y
对于在列X和Y中具有A，B的每一行，在X和Y中将始终存在具有B，A的行
Columns X and Y are of type int
列X和Y的类型为int

I need a T-Sql query that given a set with the rules above will return me either Row 1 or Row 2, but not both.

我需要一个T-Sql查询，给出一个带有上述规则的集合将返回第1行或第2行，但不是两者。

Either the answer is very difficult, or its so easy that I can't see the forest for the trees, either way it's driving me up the wall.

答案是非常困难的，或者它很容易让我无法看到森林中的树木，无论是哪种方式都让我爬上了墙。

4 个解决方案

#1

Add to your query the predicate,

添加到您的查询谓词，

where X < Y

and you can never get row two, but will always get row one.

你永远不会得到第二排，但总会得到第一排。

(This assumes that when you wrote "two given values" you meant two distinct given values; if the two values can be the same, add the predicate where X <= Y (to get rid of all "reversed" rows where X > Y) and then add a distinct to your select list (to collapse any two rows where X == Y into one row).)

（这假定当您编写“两个给定值”时，您意味着两个不同的给定值;如果两个值可以相同，则添加谓词，其中X <= Y（以消除所有“反向”行，其中X> Y ）然后在您的选择列表中添加一个distinct（将X == Y的任意两行折叠成一行）。）

In reply to comments:

在回复评论时：

That is, if currently your query is select foo, x, y from sometable where foo < 3; change it to select foo, x, y from sometable where foo < 3 and x < y;, or for the the second case (where X and Y are not distinct values) select distinct foo, x, y from sometable where foo < 3 and x <= y;.

也就是说，如果当前你的查询是从foo <3的sometable中选择foo，x，y;改变它从foo <3和x ;中选择foo，x，y，或者对于第二种情况（其中x和y不是不同的值）选择foo>

#2

This should work.

这应该工作。

Declare @t Table (PK Int Primary Key Identity(1, 1), A int, B int);

Insert into @t values (1, 2);
Insert into @t values (2, 1);
Insert into @t values (3, 4);
Insert into @t values (4, 3);
Insert into @t values (5, 6);
Insert into @t values (6, 5);

Declare @Table Table (ID Int Primary Key Identity(1, 1), PK Int, A Int, B Int);
Declare @Current Int;
Declare @A Int;

Insert Into @Table 
Select PK, A, B 
From @t;

Set @Current = 1;    

While (@Current <= (Select Max(ID) From @Table) Begin    

    Select @A = A 
    From @Table 
    Where ID = @Current;        

    If (@A Is Not Null) Begin

        Delete From @Table Where B = @A;            
        If ((Select COUNT(*) From @Table Where A = @A) > 1) Begin
            Delete From @Table Where ID = @Current;
        End

    End

    Set @A = Null;  
    Set @Current = @Current + 1;

End

Select a.*
From @tAs a
    Inner Join @Table As b On a.PK = b.PK

#3

SELECT O.X, O.Y
FROM myTable O
WHERE EXISTS (SELECT X, Y FROM myTable I WHERE I.X = O.Y AND I.Y = O.X)

I have not tried this. But, this should work.

我没试过这个。但是，这应该有效。

#4

To get the highest and lowest of each pair, you could use:

要获得每对中的最高和最低，您可以使用：

(X+Y+ABS(X-Y)) / 2 as High, (X+Y-ABS(X-Y)) / 2 as Low

So now use DISTINCT to get the pairs of them.

所以现在使用DISTINCT来获取它们的对。

SELECT DISTINCT 
  (X+Y+ABS(X-Y)) / 2 as High, (X+Y-ABS(X-Y)) / 2 as Low 
FROM YourTable

#1