SQLCLR（五）聚合

核心提示：SQL Server中的聚合，常用的比如max,count之类，SQLCLR（五）聚合， 我们现在也可以在SQLCLR里创建自定义的聚合，Visual Studio 2005中提供的聚合模板是一个结构，都可以在数据库的可编程性下相应模块里找到，这一系列到此就算是结束了，标注了[Serializable]，[SqlUse

SQL Server中的聚合，常用的比如max,count之类。 我们现在也可以在SQLCLR里创建自定义的聚合。Visual Studio 2005中提供的聚合模板是一个结构，标注了[Serializable]，[SqlUserDefinedAggregate]标签，这将让SQLCLR知道这是一个聚合函数。

看一段代码，这段代码来自SQL Server 2005联机丛书，本来自己想写一段，但突然公司有些事要做，没时间了。示例代码作用是合并同一部书(ID相同)的作者。

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

[Serializable]
[SqlUserDefinedAggregate(
　　　 Format.UserDefined, //use clr serialization to serialize the intermediate result
　　　 IsInvariantToNulls = true, //optimizer property
　　　 IsInvariantToDuplicates = false, //optimizer property
　　　 IsInvariantToOrder = false, //optimizer property
　　　 MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
　　　 /**//// <summary>
　　　 /// The variable that holds the intermediate result of the concatenation
　　　 /// </summary>
　　　 private StringBuilder intermediateResult;

　　　 /**//// <summary>
　　　 /// Initialize the internal data structures
　　　 /// </summary>
　　　 public void Init()
　　　 {
　　　　　　　 this.intermediateResult = new StringBuilder();
　　　 }

　　　 /**//// <summary>
　　　 /// Accumulate the next value, not if the value is null
　　　 /// </summary>
　　　 /// <param name="value"></param>
　　　 public void Accumulate(SqlString value)
　　　 {
　　　　　　　 if (value.IsNull)
　　　　　　　 {
　　　　　　　　　　　 return;
　　　　　　　 }

　　　　　　　 this.intermediateResult.Append(value.Value).Append(',');
　　　 }

　　　 /**//// <summary>
　　　 /// Merge the partially computed aggregate with this aggregate.
　　　 /// </summary>
　　　 /// <param name="other"></param>
　　　 public void Merge(Concatenate other)
　　　 {
　　　　　　　 this.intermediateResult.Append(other.intermediateResult);
　　　 }

　　　 /**//// <summary>
　　　 /// Called at the end of aggregation, to return the results of the aggregation.
　　　 /// </summary>
　　　 /// <returns></returns>
　　　 public SqlString Terminate()
　　　 {
　　　　　　　 string output = string.Empty;
　　　　　　　 //delete the trailing comma, if any
　　　　　　　 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());
　　　 }
}

这里有几个比较重要的方法：Terminate，这个方法是聚合最后调用的方法，它返回最后的值。可以是SQL Server的任何标量。；Accumulate，聚合每处理一行数据的时候都会调用一次，并将要处理的数据传给方法。可以在函数内部进行比如比较，合并之类的处理。；

CREATE TABLE BookAuthors
(
　　 BookID　　 int　　　　　　 NOT NULL,
　　 AuthorName　　　 nvarchar(200) NOT NULL
)

INSERT BookAuthors VALUES(1, 'Johnson')
INSERT BookAuthors VALUES(2, 'Taylor')
INSERT BookAuthors VALUES(3, 'Steven')
INSERT BookAuthors VALUES(2, 'Mayler')
INSERT BookAuthors VALUES(3, 'Roberts')
INSERT BookAuthors VALUES(3, 'Michaels')

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID

结果如下

BookID	Author Names
1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

Microsoft SQL Server Management Studio为我们提供了数据库内对象的集中管理功能，前面几篇创建的SQLCLR对象，都可以在数据库的可编程性下相应模块里找到。

这一系列到此就算是结束了，谢谢大家。

系列文章：

SQLCLR（一）入门

SQLCLR（二）存储过程和自定义函数

SQLCLR（三）触发器

SQLCLR（四）用户定义类型UDT