sqlserver中的自定义聚合函数

 

-- 测试表
create   table  Test
(F1  varchar ( 10 ),
 F2  varchar ( 10 ))
-- 插入数据
insert   into  Test 
select   ' jack '  F1, ' book1 '  F2
union
select   ' jack '  F1, ' book2 '  F2
union
select   ' jack '  F1, ' book3 '  F2
union
select   ' Mary '  F1, ' book4 '  F2
union
select   ' Mary '  F1, ' book5 '  F2
union
select   ' Mike '  F1, ' book1 '  F2
union
select   ' Mike '  F1, ' book5 '  F2
union
select   ' Mike '  F1, ' book7 '  F2
union
select   ' Mike '  F1, ' book9 '  F2
-- 一条动态SQL语句
go

解决方案 一 ：

refer to http://www.cnblogs.com/yiyanxiyin/archive/2008/09/28/1230524.html

 

-- 合并函数
CREATE   FUNCTION  MergeCharField( @Group   varchar ( 255 ))
RETURNS   varchar ( 8000 )
AS
BEGIN
DECLARE   @r   varchar ( 8000 )
SET   @r = ''
SELECT   @r = @r + ' , ' + rtrim (F2)  FROM  Test  WHERE  F1 = @Group

   note: from envykok

   将所有满足 column F1= @Group 的行的F2 column 相加

 

RETURN ( substring ( @r , 2 , 8000 ))
END
GO
-- 调用
select  F1  [ name ] ,dbo.MergeCharField(F1)  [ book ]   from  test  group   by  F1
-- 删除测试环境
drop   table  test
drop   FUNCTION  MergeCharField

/**/ /*
name     book
--------------------------
jack       book1,book2,book3
Mary     book4,book5
Mike     book1,book5,book7,book9

*/

 

解决方案 二：

refer to : http://blog.csdn.net/gaozhigang/archive/2008/12/03/3437004.aspx

 

在 SQL 中，经常需要对数据按组进行自定义的聚合操作，比如用逗号连接一系列表示 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# 的 Database 项目，项目模版选择 SQL Server 数据库。在项目管理器里添加一个 Aggregate 后，输入代码如下：

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

[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().

note: from envykok

可以将 CLR 程序集中的类型注册为用户定义的聚合函数，只要该类型实现了要求的聚合约定。此约定包括 SqlUserDefinedAggregate 属性和聚合约定方法。聚合约定包括保存聚合中间状态的机制以及累积新值的机制，后者由四种方法组成：Init、Accumulate、Merge 和 Terminate。

每个正在聚合的组会调用一次 Init 方法。该方法应根据需要清理以前使用的此实例，使其可以重新开始新的聚合计算。

正在聚合的组中的每个值会调用一次 Accumulate 方法。该方法更新实例的状态，以反映传入的参数值的累积。

Merge 方法可以用于将此聚合类的另一个实例与当前实例合并在一起。

Terminate 方法完成聚合计算并返回聚合的结果。

http://msdn.microsoft.com/zh-cn/library/ms254508%28VS.80%29.aspx

 

我们需要做的是在其中写自己的聚合逻辑即可。这几个方法的含义分别是初始化，扫描到一条记录时，合并，终止扫描。

需要注意以下几点：

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

 

 

两种解决方案的区别：

 

聚集函数必须用GROUP BY，是对本表（或连接表，下同）一批记录的数据进行计算
自定义函数一定不能用GROUP BY ，是对本表一条记录的数据进行计算。

refer to http://blog.csdn.net/zjcxc/archive/2004/07/21/47300.aspx

 

/**==** 1. 自定义函数用于group by时出错 **==**/
--一个简单的函数
create function f_str(@str sysname,@i int)
returns char(1)
as begin
 return(substring(@str,@i,1))
end
go

--下面是测试

--定义测试数据
declare @t table(name sysname)
insert into @t values('abcd')

--有group by的情况,结果错误
select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from @t
group by dbo.f_str(name,1),dbo.f_str(name,2)

--无group by的情况,结果正确
select a=dbo.f_str(name,1),b=dbo.f_str(name,2)
from @t
go

--删除测试的自定义函数
drop function f_str

/*--测试结果
a    b   
---- ----
a    a

（所影响的行数为 1 行）

a    b   
---- ----
a    b

（所影响的行数为 1 行）
--*/