首页 > 代码库 > C#写的SQL聚合函数
C#写的SQL聚合函数
SQL Server 字符串连接聚合函数.
- 注册程序集: 拷贝“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 = SAFEGO
上面的代码中, <sql安装根目录>为D:/Program Files/Microsoft SQL Server/ - 创建自定义函数:
CREATE AGGREGATE StrConcate (@input nvarchar(200)) RETURNS nvarchar(max)EXTERNAL NAME SqlStrConcate.Concatenate
- 打开SQL Server 的CLR集成.方法： SQL Server 外围应用配置器 -> 功能的外围应用配置器 -> MSSQLSERVER -> Database Engine -> CLR集成 。勾选启用CLR集成。
- OK了，现在你可以使用用户自定义字符串聚合函数StrConcate.测试代码如下:
---------------------------------------------------------------- 创建测试表--------------------------------------------------------------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 allselect 1, ‘2007-07-01‘, ‘2009-06-29‘, ‘清华紫光‘ union allselect 1, ‘2009-01-01‘, null, ‘用友软件‘union allselect 2, ‘1995-01-01‘, ‘2003-06-29‘, ‘微软中国‘union allselect 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:盛大网络
- SqlStrConcate.dll 代码（来自Sql Server的联机帮助）如下:
- 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()); } }
C#写的SQL聚合函数
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。