基于行链式关联的SQL分组实现技术咨询

嘿，这个场景我之前在项目里碰到过——本质上就是要把这种链式关联的客户交易记录按处理序列分组对吧？别担心，用SQL的递归CTE（公共表表达式）就能搞定，我给你一步步拆解：

这种链式序列的核心是找到每个链条的起点，然后顺着previous_customer_id递归关联后续所有节点，最后给同一链条的所有记录打上同一个分组标识，就能轻松完成分组了。

1. 先明确表结构（基于你的描述假设） #

假设你的交易表大概是这样的（如果实际结构有差异，调整字段名就行）：

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    customer_id INT,          -- 当前交易的客户ID
    previous_customer_id INT, -- 上一个处理的客户ID（起点记录该字段为NULL）
    user_id INT,              -- 处理这些客户的用户ID
    transaction_time DATETIME -- 交易时间，用来保证序列顺序
);

2. 用递归CTE构建链条分组 #

递归CTE分为两部分：锚点成员（找所有链条的起点）和递归成员（顺着链条遍历后续节点）：

WITH RECURSIVE customer_chains AS (
    -- 锚点成员：定位所有链条的起始记录（上一个客户ID为空的就是起点）
    SELECT 
        customer_id,
        previous_customer_id,
        user_id,
        transaction_time,
        customer_id AS chain_group_id  -- 用起点的客户ID作为该链条的唯一分组标识
    FROM transactions
    WHERE previous_customer_id IS NULL

    UNION ALL

    -- 递归成员：关联后续的交易记录，继承前面的分组标识
    SELECT 
        t.customer_id,
        t.previous_customer_id,
        t.user_id,
        t.transaction_time,
        cc.chain_group_id  -- 把起点的分组ID传递给后续所有节点
    FROM transactions t
    JOIN customer_chains cc 
        ON t.previous_customer_id = cc.customer_id
)

3. 分组聚合得到最终结果 #

有了带分组标识的customer_chains，接下来就能按分组和用户ID聚合，得到每个用户的所有处理链条：

-- 接上面的递归CTE，执行最终查询
SELECT 
    chain_group_id,
    user_id,
    -- 把同一链条的客户按处理顺序聚合（不同数据库函数略有差异）
    -- PostgreSQL用ARRAY_AGG，MySQL用GROUP_CONCAT，SQL Server用STRING_AGG
    ARRAY_AGG(customer_id ORDER BY transaction_time) AS customer_process_sequence
FROM customer_chains
GROUP BY chain_group_id, user_id;

特殊情况处理 #

• 如果你的链条起点不是previous_customer_id IS NULL，而是有特定标识（比如0），只需要把锚点成员的WHERE条件改成对应的逻辑就行。
• 如果担心链条出现循环（比如A→B→A），可以在递归成员里加一个WHERE条件排除已访问的ID，或者限制递归深度（比如PostgreSQL加LIMIT，MySQL用MAX_RECURSION_DEPTH）。
• 不同数据库的聚合函数略有差异：比如MySQL用GROUP_CONCAT(customer_id ORDER BY transaction_time SEPARATOR ',')，SQL Server用STRING_AGG(customer_id, ',') WITHIN GROUP (ORDER BY transaction_time)。

如果你的实际表结构或者业务规则有特殊细节，随时说出来我再帮你调整～

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