看一段代码,这段代码来自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, // useclrserializationtoserializetheintermediateresult
IsInvariantToNulls = true , // optimizerproperty
IsInvariantToDuplicates = false , // optimizerproperty
IsInvariantToOrder = false , // optimizerproperty
MaxByteSize = 8000 ) // maximumsizeinbytesofpersistedvalue
]
public class Concatenate:IBinarySerialize
{
/**////<summary>
///Thevariablethatholdstheintermediateresultoftheconcatenation
///</summary>
privateStringBuilderintermediateResult;
/**////<summary>
///Initializetheinternaldatastructures
///</summary>
publicvoidInit()
{
this.intermediateResult=newStringBuilder();
}
/**////<summary>
///Accumulatethenextvalue,notifthevalueisnull
///</summary>
///<paramname="value"></param>
publicvoidAccumulate(sqlStringvalue)
{
if(value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(',');
}
/**////<summary>
///Mergethepartiallycomputedaggregatewiththisaggregate.
///</summary>
///<paramname="other"></param>
publicvoidMerge(Concatenateother)
{
this.intermediateResult.Append(other.intermediateResult);
}
/**////<summary>
///Calledattheendofaggregation,toreturntheresultsoftheaggregation.
///</summary>
///<returns></returns>
publicsqlStringTerminate()
{
stringoutput=string.Empty;
//deletethetrailingcomma,ifany
if(this.intermediateResult!=null
&&this.intermediateResult.Length>0)
{
output=this.intermediateResult.ToString(0,this.intermediateResult.Length-1);
}
returnnewsqlString(output);
}
publicvoidRead(BinaryReaderr)
{
intermediateResult=newStringBuilder(r.ReadString());
}
publicvoidWrite(BinaryWriterw)
{
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, // useclrserializationtoserializetheintermediateresult
IsInvariantToNulls = true , // optimizerproperty
IsInvariantToDuplicates = false , // optimizerproperty
IsInvariantToOrder = false , // optimizerproperty
MaxByteSize = 8000 ) // maximumsizeinbytesofpersistedvalue
]
public class Concatenate:IBinarySerialize
{
/**////<summary>
///Thevariablethatholdstheintermediateresultoftheconcatenation
///</summary>
privateStringBuilderintermediateResult;
/**////<summary>
///Initializetheinternaldatastructures
///</summary>
publicvoidInit()
{
this.intermediateResult=newStringBuilder();
}
/**////<summary>
///Accumulatethenextvalue,notifthevalueisnull
///</summary>
///<paramname="value"></param>
publicvoidAccumulate(sqlStringvalue)
{
if(value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(',');
}
/**////<summary>
///Mergethepartiallycomputedaggregatewiththisaggregate.
///</summary>
///<paramname="other"></param>
publicvoidMerge(Concatenateother)
{
this.intermediateResult.Append(other.intermediateResult);
}
/**////<summary>
///Calledattheendofaggregation,toreturntheresultsoftheaggregation.
///</summary>
///<returns></returns>
publicsqlStringTerminate()
{
stringoutput=string.Empty;
//deletethetrailingcomma,ifany
if(this.intermediateResult!=null
&&this.intermediateResult.Length>0)
{
output=this.intermediateResult.ToString(0,this.intermediateResult.Length-1);
}
returnnewsqlString(output);
}
publicvoidRead(BinaryReaderr)
{
intermediateResult=newStringBuilder(r.ReadString());
}
publicvoidWrite(BinaryWriterw)
{
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对象,都可以在数据库的可编程性下相应模块里找到。
这一系列到此就算是结束了,谢谢大家。