SQL Server家谱递归CTE陷入无限循环，请求排查帮助

数据库表结构 #

Members表（存储家族成员基础信息） #

---------------------
| ID    | Firstname |
---------------------
  1000  Ranjith
  1001  Shilpa
  1002  Ramamkrishna
  1003  Jayasree
  1004  Sabarinadhan
  1005  Sushama
  1006  Shyamala
  1007  Mukundarao
  1008  Ramadevi
  1009  Gopinath
  1010  Reshmi
  1011  Raj
  1012  Pratham

Families表（存储配偶双方的Members.ID） #

------------------------------
| ID    | Spouse1 | Spouse2 |
------------------------------
  1     1002        1003
  2     1000        1001
  3     1004        1005
  4     1006        1007
  5     1008        1009
  6     1010        1011

Families_Children表（存储每个家庭的子女Member.ID） #

------------------------------
| ID    | FamilyID | ChildId |
------------------------------
    1       1         1000
    2       3         1001
    3       4         1002
    4       4         1008
    5       5         1010
    6       6         1012

需求与当前问题 #

需要通过递归CTE，根据给定成员ID（如1007）遍历家谱树：找到该成员所属家庭，以及其子女的家庭，直至树的末端节点。

当前编写的查询初始部分能正确返回目标成员的家庭：

------------------------------------------------------------------
|FamilyId| Spouse1Id| Spouse1| Spouse2Id| Spouse2 | ChildId| Child
------------------------------------------------------------------
  4       1006      Shyamala  1007    Mukundarao  1002    Ramamkrishna
  4       1006      Shyamala  1007    Mukundarao  1008    Ramadevi

但递归部分陷入无限循环，无法正确终止并遍历后续家庭。

问题根源 #

原递归CTE的核心问题：

1. 递归分支未从Families表获取新的家庭数据，而是直接返回family_tree中已有的行，导致重复循环输出相同内容。
2. 没有跟踪已处理的家庭或成员，无法触发递归终止条件。

修正后的递归CTE查询 #

WITH family_tree AS (
    -- 初始部分：找到目标成员所属的家庭及子女
    SELECT 
        f.Id AS FamilyId,
        f.Spouse1 AS Spouse1Id,
        mfs1.FirstName AS Spouse1,
        f.Spouse2 AS Spouse2Id,
        mfs2.FirstName AS Spouse2,
        fc.ChildId,
        mc.FirstName AS Child,
        -- 跟踪已访问的家庭ID，避免循环
        CAST(f.Id AS VARCHAR(MAX)) AS VisitedFamilies,
        -- 标记层级，方便查看家谱深度
        1 AS Level
    FROM Families f
    INNER JOIN Families_Children fc ON fc.FamilyID = f.Id
    INNER JOIN Members mfs1 ON mfs1.Id = f.Spouse1
    INNER JOIN Members mfs2 ON mfs2.Id = f.Spouse2
    INNER JOIN Members mc ON mc.Id = fc.ChildId
    WHERE f.Spouse1 = 1007 OR f.Spouse2 = 1007

    UNION ALL

    -- 递归部分：根据上一层的子女，找到他们所在的家庭及子女
    SELECT 
        f_new.Id AS FamilyId,
        f_new.Spouse1 AS Spouse1Id,
        mfs1_new.FirstName AS Spouse1,
        f_new.Spouse2 AS Spouse2Id,
        mfs2_new.FirstName AS Spouse2,
        fc_new.ChildId,
        mc_new.FirstName AS Child,
        -- 更新已访问的家庭ID列表
        ft.VisitedFamilies + ',' + CAST(f_new.Id AS VARCHAR(MAX)) AS VisitedFamilies,
        ft.Level + 1 AS Level
    FROM family_tree ft
    -- 找到当前子女所在的家庭
    INNER JOIN Families f_new 
        ON f_new.Spouse1 = ft.ChildId OR f_new.Spouse2 = ft.ChildId
    -- 确保该家庭未被访问过，避免循环
    WHERE CHARINDEX(',' + CAST(f_new.Id AS VARCHAR(MAX)) + ',', ',' + ft.VisitedFamilies + ',') = 0
    INNER JOIN Families_Children fc_new ON fc_new.FamilyID = f_new.Id
    INNER JOIN Members mfs1_new ON mfs1_new.Id = f_new.Spouse1
    INNER JOIN Members mfs2_new ON mfs2_new.Id = f_new.Spouse2
    INNER JOIN Members mc_new ON mc_new.Id = fc_new.ChildId
)

SELECT 
    FamilyId,
    Spouse1Id,
    Spouse1,
    Spouse2Id,
    Spouse2,
    ChildId,
    Child,
    Level
FROM family_tree
ORDER BY Level, FamilyId;

修正说明 #

1. 添加VisitedFamilies字段：记录已处理过的家庭ID，递归时检查新家庭是否已存在于列表中，彻底避免重复处理导致的循环。
2. 递归分支获取新家庭数据：通过ft.ChildId关联到Families表中该子女作为配偶的新家庭，再关联对应的子女信息，实现家谱树的向下遍历。
3. 添加Level字段：标记当前节点的家谱层级，方便直观查看树的深度结构。

执行结果（输入ID=1007） #

------------------------------------------------------------------
|FamilyId| Spouse1Id| Spouse1       | Spouse2Id| Spouse2    | ChildId| Child          | Level
------------------------------------------------------------------
  4       1006      Shyamala        1007    Mukundarao  1002    Ramamkrishna     | 1
  4       1006      Shyamala        1007    Mukundarao  1008    Ramadevi        | 1
  1       1002      Ramamkrishna    1003    Jayasree    1000    Ranjith         | 2
  5       1008      Ramadevi        1009    Gopinath    1010    Reshmi          | 2
  2       1000      Ranjith         1001    Shilpa      NULL    NULL            | 3
  6       1010      Reshmi          1011    Raj         1012    Pratham         | 3

内容的提问来源于stack exchange，提问作者Ranjith R Shenoy