sql - 合并 SQL 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         

这些是应用程序之间的关系。我想表明 Avast 和 Hitman 有关系，但从这个角度来看，我不需要表明他们在什么“方向”有关系。从这种观点来看，这种关系是双向的。

编辑: 看来我的例子太简单了。该解决方案不适用于更多数据。

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

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

根据你的问题的修改和扩展，需要更复杂的解决方案。我们形成CHECKSUM在 a1.Name1,a2.Name 上(为了获得我们交换大小的相同名称)。
使用它我们生成 ROW_NUMBER (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