SQL聚合关联账户：手动设置分组ID实现关联账户金额汇总

这个问题本质上是要找出数据里的连通分量——也就是把所有通过关联关系连在一起的账户归为一组，再计算每组的总金额。我用SQL来给你实现这个需求，假设你的两张表分别叫account_amount（存储账户与对应金额）和account_relations（存储账户间的关联关系）：

示例数据 #

首先明确我们的输入数据：

account_amount表 #

account_relations表 #

解决方案：递归CTE实现连通分量分组 #

我们可以用递归CTE（公共表表达式）来遍历所有关联关系，把连通的账户归为同一组，再关联金额表计算总和：

WITH recursive_account_groups AS (
    -- 初始化：将每个账户作为初始组，同时包含其关联账户
    SELECT 
        Account AS member_account,
        Account AS group_leader,
        Related_Account AS related
    FROM account_relations
    UNION
    -- 加入无关联关系的独立账户
    SELECT 
        Account AS member_account,
        Account AS group_leader,
        NULL AS related
    FROM account_amount
    WHERE Account NOT IN (SELECT Account FROM account_relations)
    
    UNION ALL
    
    -- 递归遍历：合并所有连通的账户组，用组内最小账户号作为组标识
    SELECT
        rag.member_account,
        LEAST(rag.group_leader, ar2.Related_Account) AS group_leader,
        ar2.Related_Account
    FROM recursive_account_groups rag
    JOIN account_relations ar2 ON rag.member_account = ar2.Account
    WHERE ar2.Related_Account <> rag.group_leader
)
-- 去重后分组计算总金额，并生成自定义组名
SELECT
    CONCAT('Account #', DENSE_RANK() OVER(ORDER BY MIN(group_leader))) AS group_name,
    SUM(aa.Amount) AS total_amount,
    STRING_AGG(aa.Account, ', ') AS group_members
FROM (
    -- 确保每个账户只对应一个组
    SELECT DISTINCT
        member_account,
        MIN(group_leader) OVER(PARTITION BY member_account) AS group_leader
    FROM recursive_account_groups
) AS account_groups
JOIN account_amount aa ON account_groups.member_account = aa.Account
GROUP BY group_leader
ORDER BY group_name;

预期输出 #

执行上述SQL后，你会得到如下结果：

补充说明 #

• 如果你的SQL方言不支持STRING_AGG()（比如MySQL），可以替换为GROUP_CONCAT(aa.Account SEPARATOR ', ')来生成组内账户列表。
• 这个方法支持任意深度的关联链，比如A关联B、B关联C、C关联D的情况，所有账户都会被归为同一组。

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