创建自定义T-SQL聚合函数：如何实现类似SUM()的求和功能

嘿，我来帮你搞定这个需求！在T-SQL里实现类似SUM()的自定义聚合，分两种场景：一种是轻量模拟（不需要CLR），适合行内多值或简单聚合；另一种是真正的CLR聚合函数，和原生SUM的行为完全一致，支持GROUP BY场景。下面给你详细拆解：

方案一：用标量/表值函数实现行内多列/值求和 #

如果你的需求是接收行内多个列（或任意数量的值）返回求和结果，不需要作为GROUP BY的聚合函数，用T-SQL自带的标量或表值函数就足够了，不用搞CLR。

1. 固定参数的标量函数 #

适合已知要计算的列数量的场景，同时处理NULL值（和SUM一致，NULL不参与计算）：

CREATE FUNCTION dbo.CustomRowSum(@val1 INT, @val2 INT, @val3 INT = 0)
-- 第三个参数设为可选，支持2列或3列求和
RETURNS INT
AS
BEGIN
    DECLARE @total INT = 0
    -- 逐个判断非NULL值再累加
    IF @val1 IS NOT NULL SET @total += @val1
    IF @val2 IS NOT NULL SET @total += @val2
    IF @val3 IS NOT NULL SET @total += @val3
    RETURN @total
END
GO

-- 使用示例：计算每一行的Col1+Col2+Col3的和
SELECT dbo.CustomRowSum(Col1, Col2, Col3) AS RowTotal
FROM YourTable;

-- 结合GROUP BY使用：先算行内和，再对分组求和
SELECT GroupColumn, SUM(dbo.CustomRowSum(Col1, Col2)) AS GroupTotal
FROM YourTable
GROUP BY GroupColumn;

2. 支持任意数量值的表值函数 #

如果需要接收不确定数量的参数，用表值参数（TVP）来实现：

-- 先创建一个表值类型，用来传递多个数值
CREATE TYPE dbo.NumberList AS TABLE (Value INT);
GO

-- 自定义求和函数
CREATE FUNCTION dbo.CustomSumTVP(@values dbo.NumberList READONLY)
RETURNS INT
AS
BEGIN
    DECLARE @total INT
    -- 直接用原生SUM来计算表值参数里的非NULL值
    SELECT @total = SUM(Value) FROM @values
    -- 所有值都是NULL时返回NULL，和原生SUM一致
    RETURN @total
END
GO

-- 使用示例：传入多个值（包括NULL）
DECLARE @nums dbo.NumberList
INSERT INTO @nums VALUES (15), (25), (NULL), (40)
SELECT dbo.CustomSumTVP(@nums) AS Total; -- 返回80，NULL被忽略

方案二：CLR自定义聚合函数（和原生SUM行为完全一致） #

如果需要像原生SUM那样，直接在GROUP BY中作为聚合函数使用（处理分组内所有行的某列值），T-SQL本身做不到，必须用CLR（.NET语言，比如C#）来实现。

1. 开启SQL Server的CLR集成 #

首先要在数据库里启用CLR支持：

-- 开启高级选项
sp_configure 'show advanced options', 1;
RECONFIGURE;
-- 启用CLR
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- 如果程序集不需要访问外部资源，这一步可以跳过；如果需要则开启
ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON;
GO

2. 编写C#聚合函数代码 #

创建一个.NET类库项目，编写如下代码（以INT类型为例，支持其他类型只需修改对应类型）：

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

// 必须标记为可序列化
[Serializable]
// 配置聚合函数的属性：Native格式性能更好，忽略NULL，结果不随重复值/顺序变化
[SqlUserDefinedAggregate(Format.Native,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = true)]
public struct CustomSum
{
    // 用Int64存储中间结果，避免INT溢出（和原生SUM的行为一致）
    private SqlInt64 _accumulatedTotal;

    // 初始化方法：聚合开始时重置总和
    public void Init()
    {
        _accumulatedTotal = 0;
    }

    // 累加方法：处理每一行的输入值
    public void Accumulate(SqlInt32 value)
    {
        // 忽略NULL值，和SUM一致
        if (!value.IsNull)
        {
            _accumulatedTotal += value.Value;
        }
    }

    // 合并方法：并行查询时合并多个分区的结果
    public void Merge(CustomSum other)
    {
        _accumulatedTotal += other._accumulatedTotal;
    }

    // 终止方法：返回最终聚合结果
    public SqlInt64 Terminate()
    {
        return _accumulatedTotal;
    }
}

如果要支持Decimal、Float等类型，只需把SqlInt32、SqlInt64换成对应的SqlDecimal、SqlDouble即可。

3. 编译并部署CLR程序集 #

把C#代码编译成DLL文件，然后部署到SQL Server：

-- 创建程序集
CREATE ASSEMBLY CustomAggregateAssembly
FROM 'C:\YourPath\CustomAggregate.dll' -- 替换成你的DLL路径
WITH PERMISSION_SET = SAFE; -- 安全权限，不需要外部访问时用SAFE即可
GO

-- 创建自定义聚合函数
CREATE AGGREGATE dbo.CustomSum(@value INT)
RETURNS BIGINT
EXTERNAL NAME CustomAggregateAssembly.CustomSum;
GO

4. 使用自定义聚合函数 #

现在就可以像用原生SUM一样使用了：

-- 分组求和
SELECT GroupColumn, dbo.CustomSum(YourColumn) AS GroupTotal
FROM YourTable
GROUP BY GroupColumn;

-- 全局求和
SELECT dbo.CustomSum(YourColumn) AS OverallTotal
FROM YourTable;

注意事项 #

• NULL处理：两种方案都保持和原生SUM一致的逻辑——忽略NULL值，所有值都是NULL时返回NULL
• 溢出问题：CLR方案用更大的中间类型（比如Int64存储INT的总和），避免溢出，和原生SUM的行为匹配
• CLR权限：尽量用PERMISSION_SET = SAFE，这是最安全的权限级别，只有需要访问外部资源时才用UNSAFE或EXTERNAL_ACCESS

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