C#写的SQL聚合函数
来源:互联网 发布:sql to_char 编辑:程序博客网 时间:2024/06/05 03:01
2009/7/19
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聚合函数
- sql的聚合函数
- sql的聚合函数
- 用 C# 开发 SQL Server 2005 的自定义聚合函数
- 用 C# 开发 SQL Server 2005 的自定义聚合函数
- 聚合函数查询的SQL
- 【SQL】null对聚合函数的影响
- sql聚合函数
- T-SQL聚合函数
- SQL聚合函数
- 聚合函数 (Entity SQL)
- sql 常用聚合函数
- sql聚合函数
- sql 常用聚合函数
- SQL 聚合函数
- SQL基础--聚合函数
- SQL 聚合函数
- SQL聚合函数
- Tomcat全攻略
- sql Unknown值
- vs2005集成sp1
- 中国行销力第一人 吕志超《领袖演说行销策略》
- POJ 1906 Three Powers
- C#写的SQL聚合函数
- showModalDialog和showModelessDialog使用
- Tomcat性能调整
- 72首经典英文歌曲排行版
- MyEclipse详解及MyEclipse7.5的优化
- 软件的alpha、beta、gamma、RC、GA版本的区别
- VS2008 调试慢的问题
- VB6初步实现在WINXP下类似WIN7显示桌面的功能
- 我的异常网 -异常汇总2009-11-28