28、T-SQL 自定义聚合计算的多种解决方案

T-SQL 自定义聚合计算的多种解决方案

在 T-SQL 中，自定义聚合计算是一项常见且重要的任务，本文将介绍多种实现自定义聚合计算的方法，包括使用透视表、FOR XML、赋值 SELECT 语句等，以及如何计算聚合乘积和众数。

1. 使用透视表处理自定义聚合

当聚合函数和列应用于字符串的 MAX 操作，但缺少扩展列时，可通过计算按 groupid 分区并按 memberid 排序的行号来解决。假设每组元素的最大数量为 4，可将这些行号作为扩展值。以下是使用透视表的解决方案查询：

SELECT groupid,
    [1]
  + COALESCE(',' + [2], '')
  + COALESCE(',' + [3], '')
  + COALESCE(',' + [4], '') AS string
FROM (SELECT groupid, string,
        ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY memberid) AS rn
      FROM dbo.Groups AS A) AS D
  PIVOT(MAX(string) FOR rn IN([1],[2],[3],[4])) AS P;

由于透视查询结果中可能存在 NULL 值，可使用 COALESCE 函数将 ',' + <element> 替换为空字符串。更优雅的方式是使用 CONCAT