SQL Server中的聚合,常用的比如max,count之类。 我们现在也可以在SQLCLR里创建自定义的聚合。Visual Studio 2005中提供的聚合模板是一个结构,标注了[Serializable],[SqlUserDefinedAggregate]标签,这将让SQLCLR知道这是一个聚合函数。
看一段代码,这段代码来自SQL Server 2005联机丛书,本来自己想写一段,但突然公司有些事要做,没时间了。示例代码作用是合并同一部书(ID相同)的作者。
Microsoft SQL Server Management Studio为我们提供了数据库内对象的集中管理功能,前面几篇创建的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,聚合每处理一行数据的时候都会调用一次,并将要处理的数据传给方法。可以在函数内部进行比如比较,合并之类的处理。;
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());
}
}
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 ' )
(
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
结果如下
FROM BookAuthors
GROUP BY BookID
BookID | Author Names |
---|---|
1 | Johnson |
2 | Taylor, Mayler |
3 | Roberts, Michaels, Steven |
Microsoft SQL Server Management Studio为我们提供了数据库内对象的集中管理功能,前面几篇创建的SQLCLR对象,都可以在数据库的可编程性下相应模块里找到。
这一系列到此就算是结束了,谢谢大家。