循环标记技术问询：成员再次出现时标记循环状态及SQL实现

根据你给出的需求和示例，我们需要识别代理转移链中的循环闭环：当某一次转移的转入代理能够通过之前的转移路径回溯到转出代理时，标记为1，否则标记为0。以下是基于SQL Server的具体实现方案：

步骤1：确认表结构并插入示例数据 #

首先确保你的基础表结构完整，我们先补全并插入示例数据：

-- 创建基础表
DROP TABLE IF EXISTS [dbo].[Basetable]
CREATE TABLE [dbo].[Basetable] (
 [Incident ID] INT,
 [Activity ID] VARCHAR(20),
 [转出代理ID] VARCHAR(20),
 [转入代理ID] VARCHAR(20),
 [移动次数] INT
)

-- 插入示例数据
INSERT INTO [dbo].[Basetable] ([Incident ID], [Activity ID], [转出代理ID], [转入代理ID], [移动次数])
VALUES
(1, '001A9552444', 'YNGBAN', 'TOMYAN', 1),
(2, '001A9552461', 'TOMYAN', 'THOKIN', 1),
(3, '001A9552466', 'THOKIN', 'MATLOV', 1),
(4, '001A9552486', 'MATLOV', 'THOKIN', 1),
(5, '001A9552499', 'THOKIN', 'YNGBAN', 1),
(6, '001A9554213', 'YNGBAN', 'IKJER', 1)

步骤2：用递归CTE实现循环标记 #

这里提供两种可行的实现方式，你可以根据实际数据规模选择：

方式一：基于路径追踪的递归CTE #

这种方式会逐步构建转移路径，实时判断当前转移是否形成闭环：

WITH TransferChain AS (
    -- 初始化第一条记录
    SELECT 
        [Incident ID],
        [Activity ID],
        [转出代理ID],
        [转入代理ID],
        [移动次数],
        CAST([转出代理ID] + '->' + [转入代理ID] AS VARCHAR(MAX)) AS TransferPath,
        0 AS 循环标记
    FROM [dbo].[Basetable]
    WHERE [Incident ID] = 1

    UNION ALL

    -- 递归处理后续每条记录
    SELECT 
        bt.[Incident ID],
        bt.[Activity ID],
        bt.[转出代理ID],
        bt.[转入代理ID],
        bt.[移动次数],
        tc.TransferPath + '->' + bt.[转入代理ID] AS TransferPath,
        -- 判断是否形成循环：两种情况
        CASE 
            -- 情况1：存在直接反向转移（如MATLOV→THOKIN，之前有THOKIN→MATLOV）
            WHEN CHARINDEX(bt.[转入代理ID] + '->' + bt.[转出代理ID], tc.TransferPath) > 0 THEN 1
            -- 情况2：形成间接闭环（如THOKIN→YNGBAN，之前有YNGBAN→TOMYAN→THOKIN）
            WHEN EXISTS (
                SELECT 1 
                FROM STRING_SPLIT(tc.TransferPath, '->') AS s
                WHERE s.value = bt.[转入代理ID]
            ) AND EXISTS (
                SELECT 1
                FROM TransferChain tc_prev
                WHERE tc_prev.[Incident ID] < bt.[Incident ID]
                AND CHARINDEX(bt.[转入代理ID], tc_prev.TransferPath) > 0
                AND CHARINDEX(bt.[转出代理ID], tc_prev.TransferPath) > 0
                AND CHARINDEX(bt.[转出代理ID], tc_prev.TransferPath) < CHARINDEX(bt.[转入代理ID], tc_prev.TransferPath)
            ) THEN 1
            ELSE 0
        END AS 循环标记
    FROM [dbo].[Basetable] bt
    JOIN TransferChain tc ON bt.[Incident ID] = tc.[Incident ID] + 1
)

-- 输出最终结果
SELECT 
    [Incident ID] AS 序列ID,
    [Activity ID],
    [转出代理ID],
    [转入代理ID],
    [移动次数],
    [循环标记]
FROM TransferChain
ORDER BY [Incident ID];

方式二：基于可达性检查的递归CTE #

这种方式先预先生成所有代理的可达关系，再批量判断循环：

WITH AllTransfers AS (
    -- 重命名字段简化后续处理
    SELECT 
        [Incident ID],
        [转出代理ID] AS FromAgent,
        [转入代理ID] AS ToAgent,
        [Activity ID],
        [移动次数]
    FROM [dbo].[Basetable]
),
ReachableAgents AS (
    -- 递归生成每个代理能到达的所有节点
    SELECT 
        FromAgent,
        ToAgent,
        FromAgent AS StartAgent,
        1 AS Depth
    FROM AllTransfers
    UNION ALL
    SELECT 
        ra.FromAgent,
        at.ToAgent,
        ra.StartAgent,
        ra.Depth + 1
    FROM ReachableAgents ra
    JOIN AllTransfers at ON ra.ToAgent = at.FromAgent
    WHERE ra.Depth < 100 -- 限制递归深度，防止无限循环
),
CycleCheck AS (
    SELECT 
        at.[Incident ID],
        at.[Activity ID],
        at.FromAgent AS [转出代理ID],
        at.ToAgent AS [转入代理ID],
        at.[移动次数],
        -- 如果转入代理能回溯到转出代理，且路径来自当前记录之前的转移，则标记为1
        CASE WHEN EXISTS (
            SELECT 1 
            FROM ReachableAgents ra
            WHERE ra.StartAgent = at.ToAgent 
              AND ra.ToAgent = at.FromAgent
              AND EXISTS (
                  SELECT 1 
                  FROM AllTransfers at_prev
                  WHERE at_prev.[Incident ID] < at.[Incident ID]
                    AND at_prev.FromAgent = ra.FromAgent
                    AND at_prev.ToAgent = ra.ToAgent
              )
        ) THEN 1 ELSE 0 END AS 循环标记
    FROM AllTransfers at
)

-- 输出最终结果
SELECT 
    [Incident ID] AS 序列ID,
    [Activity ID],
    [转出代理ID],
    [转入代理ID],
    [移动次数],
    [循环标记]
FROM CycleCheck
ORDER BY [Incident ID];

结果验证 #

执行上述任意一种SQL，都会得到你预期的结果：

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