当前数据
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在这里使它更易读.
解决方法
如果输出只能包含双向关系(‘XX”A’)AND(‘A”XX’),请尝试：
;
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 | +------+-----+--------------------------+-----------------------+