SqlServerCLR聚合函数:分组连接字符串

    今天在开源社区看到某个人问如何将表里某列字符串按照group by的分组连接成一个字符串,相当于字符串版的sum函数。想了半天没想出好办法。最后突然想起来SqlServer可以用CLR写函数,所以用C#写了个聚合函数。代码基本参考了msdn上下面的例子,喜欢原版的可以直接看msdn:
    http://msdn.microsoft.com/zh-cn/library/ms131056.aspx

    折腾步骤:

  1. 在VS2010里面创建一个Visual C# Sql CLR 数据库项目(数据库=>SQL Server=>C# Sql CLR 数据库项目),创建提示中需要输入数据库信息。
    注意将.Net Framework改成3.5版本,我的数据库是2008,最高支持到3.5,未来2012应该会支持.Net4.0。
  2. 添加一个聚合函数JoinStr.cs: 
    聚合函数
     1 using System;
    2 using System.Data;
    3 using System.Data.SqlClient;
    4 using System.Data.SqlTypes;
    5 using Microsoft.SqlServer.Server;
    6 using System.Collections.Generic;
    7 using System.Text;
    8
    9
    10 [Serializable]
    11 [SqlUserDefinedAggregate(
    12 Format.UserDefined, //use clr serialization to serialize the intermediate result
    13 IsInvariantToNulls = true, //optimizer property
    14 IsInvariantToDuplicates = false, //optimizer property
    15 IsInvariantToOrder = false, //optimizer property
    16 MaxByteSize = 8000) //maximum size in bytes of persisted value
    17 ]
    18 public class JoinStr : IBinarySerialize
    19 {
    20 private StringBuilder _strBuilder;
    21 public void Init()
    22 {
    23 _strBuilder = new StringBuilder();
    24 }
    25
    26 public void Accumulate(SqlString Value)
    27 {
    28 if (String.IsNullOrEmpty(Value.Value)) return;
    29
    30 _strBuilder.Append(Value.Value + "");
    31 }
    32
    33 public void Merge(JoinStr Group)
    34 {
    35 _strBuilder.Append(Group._strBuilder);
    36 }
    37
    38 public SqlString Terminate()
    39 {
    40 string outPut = "";
    41 if (_strBuilder != null && _strBuilder.Length > 0)
    42 {
    43 outPut = _strBuilder.ToString(0, _strBuilder.Length - 2);
    44 }
    45
    46 return new SqlString(outPut);
    47 }
    48
    49 public void Read(System.IO.BinaryReader r)
    50 {
    51 _strBuilder = new StringBuilder(r.ReadString());
    52 }
    53
    54 public void Write(System.IO.BinaryWriter w)
    55 {
    56 w.Write(_strBuilder.ToString());
    57 }
    58 }
  3. 右击项目点'生成',然后右击点'部署'。直接F5也可以。
  4. 在VS的Test.sql里或者直接在SqlServer的管理界面里新建一个查询,写入下面的sql来启用CLR功能并测试我们创建的函数:
    测试Sql
     1 --启用CLR
    2 sp_configure 'clr enabled',1;
    3 reconfigure;
    4 --看看自己写的程序集发布了没有
    5 SELECT * FROM sys.assemblies;
    6
    7 --创建测试表
    8 IF OBJECT_ID('p', 'U') IS NOT NULL
    9 DROP TABLE p;
    10 create table p(num int, name varchar(50));
    11
    12 --插入测试数据
    13 insert into p values(1,'aa');
    14 insert into p values(1,'ab');
    15 insert into p values(2,'ba');
    16 insert into p values(2,'bb');
    17 insert into p values(2,'bc');
    18 --测试CLR聚合函数
    19 select num,dbo.JoinStr(name) from dbo.p group by num;
  5. 如果没出什么意外的话会得到下面的结果:

    1 aa ab
    2 ba bb bc

     


       

 

 

 

 

posted @ 2012-03-29 16:40  李土鳖  阅读(4118)  评论(1编辑  收藏  举报