SQL Server 自定义聚合函数

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

 

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

 

环境：

    1：Sqlserver 2008 R2  

    2：Visual Studio 2013 

 在新建的sqlserver项目上右键--添加--新项目，然后选择SQL CLR C# ---- SQL CLR C# 用户自定函数， 设置好文件名后点击右下角添加按钮。

 

第一部分：

 

vs2017的界面操作如下：

 

 

 

 

 

 

 

 

.net代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

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支持的配置

?

1 2 3

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

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

?

1 2 3 4 5

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

第四步：测试

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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