在 SQL（SQL Server培訓 MySQL培訓 ） 中，經常需要對數據按組進行自定義的聚合操作，比如用逗號連接一系列表示 ID 的數字，但默認只有 SUM, MAX, MIN, AVG 等聚合函數。在 SQL Server 2005 中提供了編寫 CLR 的托管代碼的支持，我們可以用來寫自定義的聚合函數。
比如對於如下數據：
Age
Name
20
張三
21
李四
20
王二
22
趙五
18
錢六
我們想得到
Age
Name
18
錢六
20
張三,王二
21
李四
22
趙五需要實現一個聚合函數 StrJoin, 其功能是用逗號連接字符串。
預期的 SQL 語句如下：
select
Age,
dbo.StrJoin(Name) as Name
from
SomeTable
要實現這個函數，用 Visual Studio 2005 建立一個 C#（C#培訓 ） 的 Database 項目，項目模版選擇 SQL Server 數據庫。在項目管理器裡添加一個 Aggregate 後，輸入代碼如下：
using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
http://www.mscto.com
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToDuplicates=false,
IsInvariantToNulls=true,
IsInvariantToOrder=false,
IsNullIfEmpty=true,
MaxByteSize=8000
)]
public struct StrJoin: IBinarySerialize {
private StringBuilder _result;
public void Init() {
_result = new StringBuilder();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) {
return;
} else {
if (_result.Length > 0)
_result.Append(",");
_result.Append(Value.Value);
}
}
public void Merge(StrJoin Group) {
_result.Append(Group._result);
}
public SqlString Terminate() {
if (_result.Length > 0) {
return new SqlString(_result.ToString());
}
return new SqlString("");
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r) {
_result = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w) {
w.Write(_result.ToString());
}
#endregion
}
這裡不敘述詳細的操作步驟，網上應該可以搜到很多。
其原理是該類中提供了幾個模版方法：Init(), Accumulate(), Merge(), Terminate().
我們需要做的是在其中寫自己的聚合邏輯即可。這幾個方法的含義分別是初始化，掃描到一條記錄時，合並，終止掃描。
需要注意以下幾點：
1. 自定義聚集函數中，我們返回的數據會被序列化然後轉換到 SQL Server 中，對一些數值類型 Framework 提供了默認的序列化機制，但其他一些 CLR 的類型比如 string 就必須自己實現序列化機制，也就是實現 IBinarySerialize 接口。
2. 返回值和 SQL Server 裡定義的變量一樣，受到 8000 字節的長度限制。
3. SQL Server 2005 必須設置兼容性級別為 "SQL Server 2005(90)", 否則會出現如下錯誤：
'EXTERNAL' 附近有語法錯誤。您可能需要將當前數據庫的兼容級別設置為更高的值，
以啟用此功能。有關存儲過程 sp_dbcmptlevel 的信息，請參見幫助。
4. 需要開啟 SQL Server 2005 對 CLR 的支持（如果沒有打開的話）。
執行如下命令：
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO
