如何用递归SQL查询分组直接/间接共享手机号的账户

原代码问题分析 #

你写的递归CTE触发The maximum recursion 100 has been exhausted错误的核心原因是未限制已遍历的账户：

• 账户1通过手机号1111111111关联到账户2，账户2又会通过同一个手机号关联回账户1，形成无限循环
• 递归过程中反复处理同一组内的账户对，导致递归层数快速超过默认上限

修正后的SQL代码 #

下面的方案通过跟踪已访问的账户ID避免循环遍历，确保所有直接/间接共享手机号的账户被归为同一组：

WITH ConnectedAccounts AS (
    -- 基础案例：每个账户作为初始组，记录已访问的账户
    SELECT 
        a.AccountID AS GroupID,
        a.AccountID,
        a.PhoneNumber,
        -- 用字符串记录已访问的账户，避免重复遍历
        CAST(',' + CAST(a.AccountID AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS VisitedAccounts
    FROM AccountPhoneNumbers a

    UNION ALL

    -- 递归案例：关联共享手机号的未访问账户
    SELECT 
        c.GroupID,
        b.AccountID,
        b.PhoneNumber,
        -- 更新已访问账户列表
        c.VisitedAccounts + CAST(b.AccountID AS VARCHAR(10)) + ','
    FROM AccountPhoneNumbers b
    JOIN ConnectedAccounts c ON b.PhoneNumber = c.PhoneNumber
    -- 关键：只处理未加入当前组的账户
    WHERE CHARINDEX(',' + CAST(b.AccountID AS VARCHAR(10)) + ',', c.VisitedAccounts) = 0
),
-- 去重并整理每组的账户
DistinctGroups AS (
    SELECT DISTINCT
        GroupID,
        AccountID
    FROM ConnectedAccounts
)
-- 按组聚合账户
SELECT
    GroupID,
    STRING_AGG(AccountID, ',') AS ConnectedAccounts
FROM DistinctGroups
GROUP BY GroupID

逻辑说明 #

1. 基础案例：为每个账户初始化一个组，用VisitedAccounts字符串标记该组已包含的账户
2. 递归案例：通过手机号关联其他账户，但仅处理未出现在VisitedAccounts中的账户，彻底避免循环
3. 去重与聚合：通过DistinctGroups去除重复的账户-组关系，最后用STRING_AGG将同一组的账户合并为字符串

针对你的示例数据，执行后会得到如下结果：

GroupID  ConnectedAccounts
1        1,2,3
2        1,2,3
3        1,2,3

如果希望每组只保留唯一的分组标识（比如组内最小的AccountID），可以增加以下处理：

WITH ConnectedAccounts AS (
    SELECT 
        a.AccountID AS GroupID,
        a.AccountID,
        a.PhoneNumber,
        CAST(',' + CAST(a.AccountID AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS VisitedAccounts
    FROM AccountPhoneNumbers a

    UNION ALL

    SELECT 
        c.GroupID,
        b.AccountID,
        b.PhoneNumber,
        c.VisitedAccounts + CAST(b.AccountID AS VARCHAR(10)) + ','
    FROM AccountPhoneNumbers b
    JOIN ConnectedAccounts c ON b.PhoneNumber = c.PhoneNumber
    WHERE CHARINDEX(',' + CAST(b.AccountID AS VARCHAR(10)) + ',', c.VisitedAccounts) = 0
),
DistinctGroups AS (
    SELECT DISTINCT
        GroupID,
        AccountID
    FROM ConnectedAccounts
),
-- 为每个账户找到组内最小的AccountID作为唯一分组标识
UniqueGroups AS (
    SELECT
        AccountID,
        MIN(GroupID) AS UniqueGroupID
    FROM DistinctGroups
    GROUP BY AccountID
)
-- 按唯一分组标识聚合
SELECT
    UniqueGroupID AS GroupID,
    STRING_AGG(AccountID, ',') AS ConnectedAccounts
FROM UniqueGroups
GROUP BY UniqueGroupID

执行后会得到简洁的唯一分组结果：

GroupID  ConnectedAccounts
1        1,2,3

内容的提问来源于stack exchange，提问作者Alex