SQL Server中的自定义聚合函数(concat)

基础概念

SQL Server中的自定义聚合函数允许开发者创建自己的聚合操作，类似于内置的SUM、AVG、COUNT等函数。自定义concat聚合函数可以将多行中的字符串值连接成一个字符串，类似于MySQL的GROUP_CONCAT或PostgreSQL的STRING_AGG函数。

优势

灵活性：可以自定义连接字符串的分隔符、排序方式等
性能：在数据库层面处理字符串连接，减少应用层处理
一致性：确保所有客户端使用相同的字符串连接逻辑
简化代码：减少应用层代码复杂度

实现方法

在SQL Server中实现自定义concat聚合函数需要使用CLR(Common Language Runtime)集成，因为T-SQL本身不支持创建自定义聚合函数。

实现步骤

创建CLR程序集：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.IO;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, // 使用自定义序列化
    IsInvariantToNulls = true, // 聚合是否忽略NULL值
    IsInvariantToDuplicates = false, // 聚合是否对重复值敏感
    IsInvariantToOrder = false, // 聚合是否对顺序敏感
    MaxByteSize = 8000)] // 最大字节大小
public class Concat : IBinarySerialize
{
    private StringBuilder _result;
    private string _delimiter;

    public void Init()
    {
        _result = new StringBuilder();
        _delimiter = ", "; // 默认分隔符
    }

    public void Accumulate(SqlString value)
    {
        if (!value.IsNull)
        {
            if (_result.Length > 0)
                _result.Append(_delimiter);
            _result.Append(value.Value);
        }
    }

    public void Accumulate(SqlString value, SqlString delimiter)
    {
        if (!delimiter.IsNull)
            _delimiter = delimiter.Value;

        Accumulate(value);
    }

    public void Merge(Concat other)
    {
        if (other._result.Length > 0)
        {
            if (_result.Length > 0)
                _result.Append(_delimiter);
            _result.Append(other._result.ToString());
        }
    }

    public SqlString Terminate()
    {
        return new SqlString(_result.ToString());
    }

    public void Read(BinaryReader r)
    {
        _result = new StringBuilder(r.ReadString());
        _delimiter = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        w.Write(_result.ToString());
        w.Write(_delimiter);
    }
}

编译并部署程序集到SQL Server：

-- 启用CLR集成
sp_configure 'clr enabled', 1
RECONFIGURE

-- 创建程序集
CREATE ASSEMBLY ConcatAggregate FROM 'C:\path\to\ConcatAggregate.dll' 
WITH PERMISSION_SET = SAFE;

-- 创建聚合函数
CREATE AGGREGATE dbo.Concat (@input NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ConcatAggregate.Concat;

-- 创建带分隔符的重载版本
CREATE AGGREGATE dbo.Concat (@input NVARCHAR(MAX), @delimiter NVARCHAR(10)) 
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ConcatAggregate.Concat;

应用场景

报表生成：将多行数据合并为单行显示
日志聚合：合并相关日志条目
标签系统：将多个标签合并显示
数据导出：准备CSV格式的数据

使用示例

-- 基本用法
SELECT dbo.Concat(ProductName) FROM Products WHERE CategoryID = 1;

-- 使用自定义分隔符
SELECT dbo.Concat(ProductName, '|') FROM Products WHERE CategoryID = 1;

-- 与GROUP BY结合使用
SELECT 
    c.CategoryName,
    dbo.Concat(p.ProductName, ', ') AS Products
FROM 
    Categories c
JOIN 
    Products p ON c.CategoryID = p.CategoryID
GROUP BY 
    c.CategoryName;

常见问题及解决方案

权限问题：
- 错误：无法加载程序集或执行函数
- 解决：确保使用WITH PERMISSION_SET = SAFE或适当权限集

性能问题：
- 大量数据连接可能导致性能下降
- 解决：考虑限制连接的数据量或使用FOR XML PATH方法作为替代
大小限制：
- 默认最大8000字节
- 解决：调整MaxByteSize属性或处理更小的数据集
NULL值处理：
- 默认忽略NULL值
- 解决：修改Accumulate方法以包含NULL值处理逻辑

替代方案

如果不想使用CLR集成，SQL Server也提供了其他字符串连接方法：

-- 使用FOR XML PATH方法
SELECT STUFF((
    SELECT ', ' + ProductName
    FROM Products
    WHERE CategoryID = 1
    FOR XML PATH('')), 1, 2, '') AS ProductList;

-- SQL Server 2017+ 使用STRING_AGG
SELECT STRING_AGG(ProductName, ', ') 
FROM Products 
WHERE CategoryID = 1;

自定义聚合函数提供了更大的灵活性，但STRING_AGG(如果可用)通常是更简单高效的选择。