SQL Server 2012多列关联聚合查询：仅用SELECT实现分组求和

环境与限制 #

• 数据库：SQL Server 2012
• 仅允许使用SELECT语句，禁止使用存储过程

现有表结构及数据 #

期望聚合结果 #

聚合规则 #

• 行之间只要通过Field A或Field B存在关联关系，就需将这些行的Number Field求和聚合
• 结果中Field A和Field B的组合无强制要求，只需确保关联行被统一聚合

解决方案 #

这是典型的连通分量问题，需找出所有通过Field A/Field B关联的行组，再对每组求和。以下是仅用SELECT语句实现的递归CTE方案：

WITH RecursiveCTE AS (
    -- 初始化：为每行分配初始组ID（使用Row Num）
    SELECT 
        [Row Num] AS GroupID,
        [Row Num] AS OriginalRow,
        [Field A],
        [Field B],
        [Number Field]
    FROM YourTableName
    WHERE [Row Num] IS NOT NULL
    
    UNION ALL
    
    -- 递归关联：合并所有通过Field A/Field B连接的行，统一组ID为最小的初始ID
    SELECT 
        MIN(r.GroupID) AS GroupID,
        y.[Row Num] AS OriginalRow,
        y.[Field A],
        y.[Field B],
        y.[Number Field]
    FROM RecursiveCTE r
    JOIN YourTableName y 
        ON (r.[Field A] = y.[Field A] AND r.[Field A] IS NOT NULL)
        OR (r.[Field B] = y.[Field B] AND r.[Field B] IS NOT NULL)
    WHERE y.[Row Num] NOT IN (SELECT OriginalRow FROM RecursiveCTE)
    GROUP BY y.[Row Num], y.[Field A], y.[Field B], y.[Number Field]
),
GroupedRows AS (
    -- 去重并确定每个行所属的最终组ID
    SELECT 
        MIN(GroupID) AS GroupID,
        [Field A],
        [Field B],
        [Number Field]
    FROM RecursiveCTE
    GROUP BY [Field A], [Field B], [Number Field], OriginalRow
)
-- 按组聚合求和，选取组内任意非空的Field A/Field B组合展示
SELECT 
    MAX(CASE WHEN [Field A] IS NOT NULL THEN [Field A] END) AS [Field A],
    MAX(CASE WHEN [Field B] IS NOT NULL THEN [Field B] END) AS [Field B],
    SUM([Number Field]) AS [Number Field Sum]
FROM GroupedRows
GROUP BY GroupID
ORDER BY [Number Field Sum] DESC;

说明 #

• 将YourTableName替换为实际表名
• 递归CTE会遍历所有关联行，把同一连通分量的行标记为同一个GroupID
• 最终聚合时选取组内非空的Field A/Field B组合，符合结果展示要求

内容的提问来源于stack exchange，提问作者D.G. Redd