C#写的SQL聚合函数

 

2009/7/19

SQL Server 字符串连接聚合函数.

1. 注册程序集:
   拷贝“SqlStrConcate.dll”至<sql安装根目录>/MSSQL.1/MSSQL/Binn目录下,执行下面的SQL:

   CREATE ASSEMBLY [SqlStrConcate]
   AUTHORIZATION [dbo]
   FROM 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn/SqlStrConcate.dll'
   WITH PERMISSION_SET = SAFE
   GO

   上面的代码中, <sql安装根目录>为D:/Program Files/Microsoft SQL Server/
2. 创建自定义函数:

   CREATE AGGREGATE StrConcate (@input nvarchar(200)) RETURNS nvarchar(max)
   EXTERNAL NAME SqlStrConcate.Concatenate

3. 打开SQL Server 的CLR集成.方法：
   SQL Server 外围应用配置器 -> 功能的外围应用配置器 -> MSSQLSERVER -> Database Engine -> CLR集成 。勾选启用CLR集成。
4. OK了，现在你可以使用用户自定义字符串聚合函数StrConcate.测试代码如下:

5. --------------------------------------------------------------
   -- 创建测试表
   --------------------------------------------------------------
   create table test_tb(pk_val  int, startdate varchar(10), enddate VARCHAR(10), corpname VARCHAR(20))
   
   --------------------------------------------------------------
   -- 插入测试数据
   --------------------------------------------------------------
   insert into test_tb     
   select 1, '2005-01-01', '2007-06-29', '方正科技'
   union all
   select 1, '2007-07-01', '2009-06-29', '清华紫光' 
   union all
   select 1, '2009-01-01', null, '用友软件'
   union all
   select 2, '1995-01-01', '2003-06-29', '微软中国'
   union all
   select 2, '2004-07-01', '2009-06-29', '盛大网络'  
   go
   
   --------------------------------------------------------------
   -- 查询测试
   --------------------------------------------------------------
   select pk_val, dbo.StrConcate(startdate + '~' + isnull(enddate, '至今') + ':' + corpname) lvl_str from test_tb group by pk_val
    
   --------------------------------------------------------------
   -- 查询结果
   --------------------------------------------------------------
   pk_val      lvl_str
   ----------- -----------------------------------------------------------------------------------------
   1           2005-01-01~2007-06-29:方正科技,2007-07-01~2009-06-29:清华紫光,2009-01-01~至今:用友软件
   2           1995-01-01~2003-06-29:微软中国,2004-07-01~2009-06-29:盛大网络
   

6. SqlStrConcate.dll 代码（来自Sql Server的联机帮助）如下:
7. 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());
       }
   }