SQL Server中基于关联PartyID的金额求和与车牌分组需求

要实现这种间接关联的分组聚合，本质是要找出所有通过LicensePlate连接的PartyID连通分量（Connected Components），然后对每个分量内的Amount求和并收集唯一车牌。以下是具体实现步骤：

方法思路 #

1. 识别连通分量：使用递归CTE（Common Table Expression）构建PartyID之间的关联关系，即使是间接关联的PartyID也会被归入同一分组（用分量中最小的PartyID作为分组标识）。
2. 聚合结果：基于分组标识，汇总每个分量的总Amount，并收集所有唯一的LicensePlate。

完整SQL代码 #

适用于SQL Server 2017及以上版本（支持STRING_AGG） #

WITH ConnectedParties AS (
    -- 锚点成员：每个PartyID初始作为自己的分组根节点
    SELECT 
        PartyID, 
        PartyID AS GroupID
    FROM PartyLicenses
    GROUP BY PartyID

    UNION ALL

    -- 递归成员：通过共享车牌扩展关联关系，更新分组为最小的根节点
    SELECT 
        cp.PartyID, 
        MIN(cp2.GroupID) AS GroupID
    FROM ConnectedParties cp
    JOIN PartyLicenses pl1 ON cp.PartyID = pl1.PartyID
    JOIN PartyLicenses pl2 ON pl1.LicensePlate = pl2.LicensePlate AND pl2.PartyID != cp.PartyID
    JOIN ConnectedParties cp2 ON pl2.PartyID = cp2.PartyID
    WHERE cp.GroupID > cp2.GroupID -- 确保向最小根节点收敛
    GROUP BY cp.PartyID
),
FinalGroups AS (
    -- 确定每个PartyID最终的分组标识（分量中最小的PartyID）
    SELECT 
        PartyID, 
        MIN(GroupID) AS GroupID
    FROM ConnectedParties
    GROUP BY PartyID
)
-- 聚合每个分组的结果
SELECT 
    STRING_AGG(DISTINCT pl.LicensePlate, ', ') AS LicensePlates,
    SUM(pl.Amount) AS [Total Amount]
FROM FinalGroups fg
JOIN PartyLicenses pl ON fg.PartyID = pl.PartyID
GROUP BY fg.GroupID
ORDER BY [Total Amount] DESC;

适用于SQL Server 2016及以下版本（使用XML拼接字符串） #

WITH ConnectedParties AS (
    SELECT 
        PartyID, 
        PartyID AS GroupID
    FROM PartyLicenses
    GROUP BY PartyID

    UNION ALL

    SELECT 
        cp.PartyID, 
        MIN(cp2.GroupID) AS GroupID
    FROM ConnectedParties cp
    JOIN PartyLicenses pl1 ON cp.PartyID = pl1.PartyID
    JOIN PartyLicenses pl2 ON pl1.LicensePlate = pl2.LicensePlate AND pl2.PartyID != cp.PartyID
    JOIN ConnectedParties cp2 ON pl2.PartyID = cp2.PartyID
    WHERE cp.GroupID > cp2.GroupID
    GROUP BY cp.PartyID
),
FinalGroups AS (
    SELECT 
        PartyID, 
        MIN(GroupID) AS GroupID
    FROM ConnectedParties
    GROUP BY PartyID
),
DistinctLicenses AS (
    -- 先获取每个分组的唯一车牌
    SELECT DISTINCT 
        fg.GroupID, 
        pl.LicensePlate
    FROM FinalGroups fg
    JOIN PartyLicenses pl ON fg.PartyID = pl.PartyID
)
-- 拼接车牌并求和
SELECT 
    STUFF((
        SELECT ', ' + LicensePlate 
        FROM DistinctLicenses dl 
        WHERE dl.GroupID = fg.GroupID 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS LicensePlates,
    SUM(pl.Amount) AS [Total Amount]
FROM FinalGroups fg
JOIN PartyLicenses pl ON fg.PartyID = pl.PartyID
GROUP BY fg.GroupID
ORDER BY [Total Amount] DESC;

代码说明 #

1. ConnectedParties CTE：递归构建PartyID的关联网络，通过共享车牌不断扩展分组，最终每个PartyID会关联到其分量中最小的PartyID作为分组标识。
2. FinalGroups CTE：确保每个PartyID只保留一个最终的分组标识，避免递归过程中产生的重复分组。
3. 聚合阶段：对每个分组，收集所有唯一车牌并拼接成字符串，同时累加所有Amount得到总金额。

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