如何基于两列组合在SQL中分配唯一ID？

你的需求本质是识别连通分量：所有通过colA/colB直接或间接关联的行，需要分配同一个唯一ID。你之前的SQL只能处理直接关联的情况，无法覆盖间接关联的链式场景，下面是正确的实现方案：

标准SQL实现（支持递归CTE的数据库：PostgreSQL、SQL Server、MySQL 8+等） #

WITH RECURSIVE connected_components AS (
    -- 基础步骤：初始化每一行的节点集合，标记行号
    SELECT 
        colA, 
        colB,
        ROW_NUMBER() OVER () AS rn,
        ARRAY[colA, colB] AS nodes
    FROM test

    UNION ALL

    -- 递归步骤：合并所有关联的连通分量
    SELECT 
        cc.colA, 
        cc.colB,
        cc.rn,
        -- 合并节点并去重
        ARRAY(SELECT DISTINCT unnest(ARRAY[cc.nodes, cc2.nodes]))
    FROM connected_components cc
    JOIN connected_components cc2 ON 
        -- 判断两个分量是否有公共节点（存在关联）
        EXISTS (SELECT 1 FROM unnest(cc.nodes) n WHERE n = ANY(cc2.nodes))
        -- 避免循环处理，只合并行号更小的分量
        AND cc.rn > cc2.rn
),
-- 提取每个连通分量的最小行号作为分组标识
component_groups AS (
    SELECT 
        colA, 
        colB,
        MIN(rn) AS group_id
    FROM connected_components
    GROUP BY colA, colB
)
-- 对分组标识做DENSE_RANK生成最终唯一ID
SELECT 
    colA, 
    colB,
    DENSE_RANK() OVER (ORDER BY group_id) AS id
FROM component_groups
ORDER BY rn;

不同数据库适配调整 #

• MySQL 8+：不支持数组类型，可改用字符串拼接（结合GROUP_CONCAT去重），递归步骤用FIND_IN_SET判断节点关联
• SQL Server：用STRING_SPLIT和STRING_AGG处理节点集合，替换数组相关语法

原代码问题说明 #

你之前的SQL仅通过自连接对比当前行与之前行的直接匹配，只能处理相邻的关联场景，无法覆盖链式间接关联（比如(A,B)→(B,C)→(C,D)这类关联链，原代码会给第三行分配新ID，而正确逻辑应该归为同一ID）。递归CTE可以遍历所有连通节点，把整个关联链归为同一分组。

内容的提问来源于stack exchange，提问作者NAMITHA ANTONY 1740250