sql - 递归 SQL 查询查找所有匹配的标识符

Question

我有一个具有以下结构的表格

CREATE TABLE Source
(
     [ID1] INT, 
     [ID2] INT
);

INSERT INTO Source ([ID1], [ID2]) 
VALUES (1, 2), (2, 3), (4, 5),
       (2, 5), (6, 7)

源表和结果表示例:

源表主要存储哪个 id 与另一个 id 匹配。从图中可以看出1、2、3、4、5是相同的。 6、7 是相同的。我需要一个 SQL 查询来获取包含 id 之间所有匹配项的结果表。

我在网站上找到了这个项目 - Recursive query in SQL Server与我的任务类似，但结果不同。

我尝试编辑我的任务的代码，但它不起作用。 “语句终止。在语句完成之前，最大递归 100 已用完。”

;WITH CTE
AS
(
    SELECT DISTINCT
        M1.ID1,
        M1.ID1 as ID2
    FROM Source M1
        LEFT JOIN Source M2
            ON M1.ID1 = M2.ID2
    WHERE M2.ID2 IS NULL
    UNION ALL
    SELECT
        C.ID2,
        M.ID1
    FROM CTE C
        JOIN Source M
            ON C.ID1 = M.ID1
)
SELECT * FROM CTE ORDER BY ID1

非常感谢您的帮助!

Answer 1

这是一个具有挑战性的问题。您正在尝试从两个方向遍历图表。有两个关键想法:

• 添加“反向”边，使图形的行为类似于有向图，但边在两个方向上。
• 保留已访问过的边的列表。在 SQL Server 中，字符串是一种方法。

所以:

with s as (
      select id1, id2 from source
      union  -- on purpose
      select id2, id1 from source
     ),
     cte as (
      select s.id1, s.id2, ',' + cast(s.id1 as varchar(max)) + ',' + cast(s.id2 as varchar(max)) + ',' as ids
      from s
      union all
      select cte.id1, s.id2, ids + cast(s.id2 as varchar(max)) + ','
      from cte join
           s
           on cte.id2 = s.id1
      where cte.ids not like '%,' + cast(s.id2 as varchar(max)) + ',%'
     )
select *
from cte
order by 1, 2;

这是一个db<>fiddle .