-- 测试表
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 语句如下:
Age,
dbo.StrJoin (Name) as Name
from
SomeTable
要实现这个函数,用 Visual Studio 2005 建立一个 C# 的 Database 项目,项目模版选择 SQL Server 数据库。在项目管理器里添加一个 Aggregate 后,输入代码如下:
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)", 否则会出现如下错误:
以启用此功能。有关存储过程 sp_dbcmptlevel 的信息,请参见帮助。
4. 需要开启 SQL Server 2005 对 CLR 的支持(如果没有打开的话)。
执行如下命令:
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 行)
--*/