SQLite按两列拼接分组同时将foo列相同值用户归入同一组实现方法

实现思路 #

你需要的分组逻辑属于连通分量匹配，存在两层传递规则：

• 规则1：city+age相同的行默认属于同一组
• 规则2：foo值相同的行属于同一组
• 传递性：若A和B同组、B和C同组，则A和C自动同组
  普通窗口函数dense_rank无法直接处理这种跨维度的传递匹配，需要用递归CTE先计算所有连通的行集合，再生成组号。

完整SQL代码 #

WITH base AS (
    -- 预计算h = city + age 列
    SELECT *, city || age AS h FROM t
),
recursive_groups AS (
    -- 锚点：每行初始组根节点为自身id
    SELECT id AS root_id, id, h, foo FROM base
    UNION ALL
    -- 递归关联：满足h相同或foo相同的行合并到同一根节点
    SELECT r.root_id, b.id, b.h, b.foo
    FROM recursive_groups r
    JOIN base b 
        ON (r.h = b.h OR r.foo = b.foo) 
        AND b.id < r.root_id
)
-- 最终按根节点编号生成组号
SELECT 
    DENSE_RANK() OVER (ORDER BY MIN(root_id)) AS grpnum,
    name,
    id,
    city,
    age,
    foo
FROM base b
JOIN recursive_groups rg ON b.id = rg.id
GROUP BY b.id, name, city, age, foo
ORDER BY grpnum;

输出结果验证 #

和你给出的示例逻辑一致：

• grpnum = 1：Alice、Bob、Jack、Simone
• grpnum = 2：Eve

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