在 SQL 中，经常需要对数据按组进行自定义的聚合操作，比如用逗号连接一系列表示 ID 的数字，但默认只有 SUM, MAX, MIN, AVG 等聚合函数。在 SQL Server 2005 中提供了编写 CLR 的托管代码的支持，我们可以用来写自定义的聚合函数。
比如对于如下数据：www.goodsgy.com
| Age | Name |
| 20 | 张三 |
| 21 | 李四 |
| 20 | 王二 |
| 22 | 赵五 |
| 18 | 钱六 |
www.goodsgy.com我们想得到www.goodsgy.com
| Age | Name |
| 18 | 钱六 |
| 20 | 张三,王二 |
| 21 | 李四 |
| 22 | 赵五 |
需要实现一个聚合函数 StrJoin, 其功能是用逗号连接字符串。
预期的 SQL 语句如下：
select
Age,
dbo.StrJoin(Name) as Name
from
SomeTable
要实现这个函数，用 Visual Studio 2005 建立一个 C# 的 Database 项目，项目模版选择 SQL Server 数据库。在项目管理器里添加一个 Aggregate 后，输入代码如下：www.goodsgy.comusing System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;www.goodsgy.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;www.goodsgy.com public void Init() {
_result = new StringBuilder();
}www.goodsgy.com public void Accumulate(SqlString Value) {
if (Value.IsNull) {
return;
} else {
if (_result.Length > 0)
_result.Append(",");
_result.Append(Value.Value);
}
}www.goodsgy.com public void Merge(StrJoin Group) {
_result.Append(Group._result);
}www.goodsgy.com public SqlString Terminate() {
if (_result.Length > 0) {
return new SqlString(_result.ToString());
}
return new SqlString("");
}www.goodsgy.com #region IBinarySerialize Memberswww.goodsgy.com public void Read(System.IO.BinaryReader r) {
_result = new StringBuilder(r.ReadString());
}www.goodsgy.com public void Write(System.IO.BinaryWriter w) {
w.Write(_result.ToString());
}www.goodsgy.com #endregion
}www.goodsgy.com |
www.goodsgy.com这里不叙述详细的操作步骤，网上应该可以搜到很多。
其原理是该类中提供了几个模版方法：Init(), Accumulate(), Merge(), Terminate().
我们需要做的是在其中写自己的聚合逻辑即可。这几个方法的含义分别是初始化，扫描到一条记录时，合并，终止扫描。www.goodsgy.com
需要注意以下几点：www.goodsgy.com
1. 自定义聚集函数中，我们返回的数据会被序列化然后转换到 SQL Server 中，对一些数值类型 Framework 提供了默认的序列化机制，但其他一些 CLR 的类型比如 string 就必须自己实现序列化机制，也就是实现 IBinarySerialize 接口。www.goodsgy.com
2. 返回值和 SQL Server 里定义的变量一样，受到 8000 字节的长度限制。www.goodsgy.com
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
GOwww.goodsgy.com
http://www.cnblogs.com/RChen/archive/2006/11/15/sql2k5_clr.htmlwww.goodsgy.com
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus();){kind=link}