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

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,'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

然而,这是有用的,所以我想这是一个搞砸我的指南.

再次编辑：

我没有看过这段时间,我以为它的工作,但我只是意识到它没有.我一整天都在努力,但我必须承认,SQL并不是我的强大套件.事情就是这样

DECLARE @a TABLE (ID int,'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( 5,'Hola! Better Internet' )
INSERT  INTO @a VALUES  ( 6,'avast' )       
INSERT  INTO @a VALUES  ( 7,'ZenMate' )
INSERT  INTO @a VALUES  ( 9,'XX','A' )  
INSERT  INTO @a VALUES  ( 10,'BB' )  
INSERT  INTO @a VALUES  ( 11,'BB','XX' )
INSERT  INTO @a VALUES  ( 12,'A','XX' )
INSERT  INTO @a VALUES  ( 13,'CC' )  
INSERT  INTO @a VALUES  ( 14,'CC','XX' )

;With CTE as
(
SELECT  a1.ID AS ID1,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在这里使它更易读.

解决方法

;
WITH m    (ID1,Name2) AS (
    SELECT ID1,Name2
    FROM (
        SELECT a1.ID AS ID1,a1.Name1 AS Name1,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,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                    |
+------+-----+--------------------------+-----------------------+

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!