在Server中合并同一表中的双向关系

Question

当前数据

ID      | Name1      | Name2
<guid1> | XMind      | MindNode    
<guid2> | MindNode   | XMind         
<guid3> | avast      | Hitman Pro    
<guid4> | Hitman Pro | avast
<guid5> | PPLive     | Hola!    
<guid6> | ZenMate    | Hola!    
<guid7> | Hola!      | PPLive
<guid8> | Hola!      | ZenMate  

所需输出

ID1       | ID2       | Name1  | Name2
<guid1>   | <guid2>   | XMind  | MindNode 
<guid3>   | <guid4>   | avast  | Hitman Pro  
<guid5>   | <guid7>   | PPLive | Hola!
<guid6>   | <guid8>   | Hola!  | ZenMate         

这是应用程序之间的关系。我想展示的是，阿凡达和赫曼之间有一种关系，但在这个观点中，我不需要说明他们之间的关系是什么“方向”。在这种观点中，这种关系是双向的。

编辑：似乎是我的例子太简单了。解决方案不适用于更多的数据。

DECLARE @a TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'Hitman Pro', 'avast' )
INSERT  INTO @a VALUES  ( 5, 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( 6, 'ZenMate', 'Hola! Better Internet' )       
INSERT  INTO @a VALUES  ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( 8, 'Hola! Better Internet', 'ZenMate' )

SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2
FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
        AND a1.ID < a2.ID -- avoid duplicates

然而，这是可行的，所以我想是Guid在捣乱我。

再次编辑：

我已经有一段时间没有看过这个了，我认为它很有效，但我只是意识到它不管用。整个上午我都在努力解决这个问题，但我必须承认，SQL并不是我真正的强大套件。问题是这个。

DECLARE @a TABLE (ID int, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( 5, 'ZenMate', 'Hola! Better Internet' )
INSERT  INTO @a VALUES  ( 6, 'Hitman Pro', 'avast' )       
INSERT  INTO @a VALUES  ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( 8, 'Hola! Better Internet', 'ZenMate' )
INSERT  INTO @a VALUES  ( 9, 'XX', 'A' )  
INSERT  INTO @a VALUES  ( 10, 'XX', 'BB' )  
INSERT  INTO @a VALUES  ( 11, 'BB', 'XX' )
INSERT  INTO @a VALUES  ( 12, 'A', 'XX' )
INSERT  INTO @a VALUES  ( 13, 'XX', 'CC' )  
INSERT  INTO @a VALUES  ( 14, 'CC', 'XX' )

;With CTE as
(
SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2,
        CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, -- just for display
        Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)
                           order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn

FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
)
Select ID1, ID2,Name1, Name2
from CTE C1
where rn=1

当我使用这段代码时，它确实可以很好地处理名称，但它与ID不匹配。

结果是

ID1       | ID2       | Name1  | Name2
12        | 9         | A      | X (Correct)
7         | 5         | Hola!  | ZenMate (Not Correct)   
[..]

我已经拔了整个早上的头发，但我弄不明白。我仍然使用Guid作为ID，只使用Int在这里使它更易读。

Answer 1

如果输出应该只包含双向关系('XX' + 'A') AND ('A' + 'XX')，请尝试如下：

;
WITH m    (ID1, ID2, Name1, Name2) AS (
    SELECT ID1, ID2, Name1, Name2
    FROM (
        SELECT a1.ID AS ID1
              ,a2.ID AS ID2
              ,a1.Name1 AS Name1
              ,a2.Name1 AS Name2
              ,ROW_NUMBER() OVER (PARTITION BY a1.Name1, a2.Name1 ORDER BY (SELECT 1)) AS n
        FROM @a AS a1
        JOIN @a AS a2
            ON a1.Name1 = a2.Name2
                AND a1.Name2 = a2.Name1
    ) AS T
    WHERE n = 1
)
SELECT DISTINCT *
FROM (
    SELECT ID1, ID2, Name1, Name2
    FROM m
    WHERE ID1 <= ID2

    UNION ALL

    SELECT ID2, ID1, Name2, Name1
    FROM m
    WHERE ID1 > ID2
) AS dm

它产生的产出如下：

+------+-----+--------------------------+-----------------------+
| ID1  | ID2 | Name1                    | Name2                 |
+------+-----+--------------------------+-----------------------+
|    1 |   2 | XMind                    | MindNode              |
|    3 |   6 | avast                    | Hitman Pro            |
|    4 |   7 | PPLive Video Accelerator | Hola! Better Internet |
|    5 |   8 | ZenMate                  | Hola! Better Internet |
|    9 |  12 | XX                       | A                     |
|   10 |  11 | XX                       | BB                    |
|   13 |  14 | XX                       | CC                    |
+------+-----+--------------------------+-----------------------+

Answer 2

DECLARE @a TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'Hitman Pro', 'avast' )

SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2
FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
        AND a1.ID < a2.ID -- avoid duplicates

关于你的问题的修正和扩展，需要一个更复杂的解决办法。我们在1.name1，a2.Name上形成一个校验和 (为了获得我们在大小上交换的相同的名称)。

使用它，我们用编号(Transact-SQL)生成一个数字，并且只使用数字1的结果中的行。

DECLARE @a TABLE (ID uniqueIdentifier, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( NewID(), 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( NewID(), 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( NewID(), 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( NewID(), 'Hitman Pro', 'avast' )
INSERT  INTO @a VALUES  ( NewID(), 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( NewID(), 'ZenMate', 'Hola! Better Internet' )       
INSERT  INTO @a VALUES  ( NewID(), 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( NewID(), 'Hola! Better Internet', 'ZenMate' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'A' )  
INSERT  INTO @a VALUES  ( NewID(), 'A', 'XX' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'BB' )  
INSERT  INTO @a VALUES  ( NewID(), 'BB', 'XX' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'CC' )  
INSERT  INTO @a VALUES  ( NewID(), 'CC', 'XX' )


;With CTE as
(
SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2,
        CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, -- just for display
        Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)
                           order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn

FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
)
Select *
from CTE C1
where rn=1

编辑：

如果您只想得到两个字段都适合的查询，那么只需：

SELECT a1.ID AS ID1 , a2.ID AS ID2 , a1.Name1 , a2.Name1 AS Name2 
FROM @a a1 
JOIN @a a2 ON a1.Name1 = a2.Name2 and a1.Name2 = a2.Name1 AND a1.ID < a2.ID

Answer 3

只需使用ROW_NUMBER函数对行进行排序，并在join中使用此级别，而不是使用原始的ID列：

DECLARE @a TABLE (ID UNIQUEIDENTIFIER, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( NEWID(), 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( NEWID(), 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( NEWID(), 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( NEWID(), 'Hitman Pro', 'avast' )
INSERT  INTO @a VALUES  ( NEWID(), 'PPLive Video Accelerator', 'Hola! Better Internet' )
INSERT  INTO @a VALUES  ( NEWID(), 'ZenMate', 'Hola! Better Internet' )       
INSERT  INTO @a VALUES  ( NEWID(), 'Hola! Better Internet', 'PPLive Video Accelerator' )
INSERT  INTO @a VALUES  ( NEWID(), 'Hola! Better Internet', 'ZenMate' )

;WITH cte AS(SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM @a)
SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2
FROM    cte a1
JOIN cte a2 ON a1.Name1 = a2.Name2 AND 
               a2.Name1 = a1.Name2 AND 
               a1.rn < a2.rn

输出：

ID1     ID2     Name1                       Name2
Guid    Guid    XMind                       MindNode
Guid    Guid    avast                       Hitman Pro
Guid    Guid    PPLive Video Accelerator    Hola! Better Internet
Guid    Guid    ZenMate                     Hola! Better Internet