SQL Server 自定义聚合函数

时间:2022-10-06 07:27:34

说明:本文依据网络转载整理而成,因为时间关系,其中原理暂时并未深入研究,只是整理备份留个记录而已。

目标:在SQL Server中自定义聚合函数,在Group BY语句中 ,不是单纯的SUM和MAX等运算,可以加入拼接字符串。

环境:

1:Sqlserver 2008 R2

2:Visual Studio 2013

第一部分:

.net代码:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text; [Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //使用UserDefined 序列化格式
IsInvariantToNulls = true, //聚合是否与空值有关
IsInvariantToDuplicates = false, //聚合是否与重复值有关
IsInvariantToOrder = false, //聚合是否与顺序有关
MaxByteSize = 8000) //聚合实例的最大大小(以字节为单位)
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// 定义变量
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// 初始化
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
} /// <summary>
/// 如果某一个字符不为空,用";"追加
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value,string contChar) //symbol
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(contChar);
}
/// <summary>
/// 合并字符
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
} /// <summary>
/// 处理最后的","
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//删除最后的","
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
} return new SqlString(output);
} public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
} public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

编译生成DLL,注意:SqlServer 2008 R2  不支持.Net Framework 4.5 ,所以生成dll的时候选在.net framework 3.5

第二步:启用数据库对CLR支持的配置

EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO

第三步:加载CLR程序集并创建自定义函数

USE Test   --选择数据库
CREATE ASSEMBLY SQL_Aggregate FROM 'E:\WorkSpace\LetMeTry\WindowsFormsApplication2\SqlCustomFunction\bin\Debug\SqlCustomFunction.dll' --生成的DLL路径
GO
CREATE AGGREGATE SQL_Aggregate (@input nvarchar(200),@contChar nvarchar(1)) RETURNS nvarchar(max)
EXTERNAL NAME SQL_Aggregate.Concatenate

第四步:测试

USE Test

--创建测试数据
create table tb(ID int,Name varchar(10))
insert into tb
select 1,'a'
union all select 1,'b'
union all select 2,'c'
union all select 2,'e'
union all select 3,'d'
go --自定义聚合函数使用例子(第二个参数为拼接字符串的连接符)
select id,dbo.SQL_Aggregate([Name],'+') AS Test
from tb
group by id