【交流】SQL 2005溢用之:合并列值 20zjcxc]

发布时间:2016-12-11 12:25:01 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"【交流】SQL 2005溢用之:合并列值 20zjcxc]",主要涉及到【交流】SQL 2005溢用之:合并列值 20zjcxc]方面的内容,对于【交流】SQL 2005溢用之:合并列值 20zjcxc]感兴趣的同学可以参考一下。

无论是在sql 2000, 还是在 sql 2005 中,都没有提供字符串的聚合函数, 所以, 当我们在处理下列要求时,会比较麻烦: 有表tb, 如下: id    value ----- ------ 1     aa 1     bb 2     aaa 2     bbb 2     ccc 需要得到结果: id     values ------ ----------- 1      aa,bb 2      aaa,bbb,ccc 即, group by id, 求 value 的和(字符串相加) 这个问题的一般处理方法是, 写一个聚合函数: create function dbo.f_str(@id int) returns varchar(8000) as begin    declare @r varchar(8000)    set @r=''    select @[email protected]+','+value from tb where [email protected]    return stuff(@r,1,1,'') end go -- 调用函数 select id, values=dbo.f_str(id) from tb group by id 这样的问题是,函数不通用,必须为每个要处理的表编写相应的处理函数 在SQL2005中,这个问题的更好解决办法是写一个CLR函数,用于实现字符串的聚合,这样可以解决通用性的问题了。

而在我下面要实现的, 是只用一条SQL语句来完成这个功能 (绝对是一条, 不会是用EXEC()取巧的那种) -- 示例数据 DECLARE @t TABLE(id int, value varchar(10)) INSERT @t SELECT 1, 'aa' UNION ALL SELECT 1, 'bb' UNION ALL SELECT 2, 'aaa' UNION ALL SELECT 2, 'bbb' UNION ALL SELECT 2, 'ccc' -- 查询处理 SELECT * FROM( SELECT DISTINCT  id FROM @t )A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N /*--结果 id          values ----------- ---------------- 1           aa,bb 2           aaa,bbb,ccc (2 行受影响) --*/

SQL 2005新增了xml数据类型, 而且xml数据类型可以方便的与字符类型之间做转换,上面的方法只是巧妙地利用了这一点,结合字符串的一些处理函数就出来结果了

-- 下面这个示例也是合并字符串的, 以系统表为列, 合并两列 SELECT * FROM( SELECT DISTINCT  type FROM sys.objects )O OUTER APPLY( SELECT names = STUFF(REPLACE(REPLACE( ( SELECT object_id, name FROM sys.objects N WHERE type = O.type FOR XML AUTO ), '<N ', ','), '/>', ''), 1, 1, '') )N

我的跟你差不多。 SELECT     vis2.ssn, vis2.cn, vis2.sgid, t .ScSeq FROM         cdwldbo.vwidsr2 vis2 CROSS apply                           (SELECT     TOP 100 PERCENT vis1.scid[data()]                             FROM          cdwldbo.vwidsr1 vis1                             WHERE      vis1.ssn = vis2.ssn AND vis1.cn = vis2.cn                             ORDER BY vis1.scid FOR xml path('')) t (ScSeq) WHERE     vis2.sccount > 1

>>在SQL2005中,这个问题的更好解决办法是写一个CLR函数,用于实现字符串的聚合,这样可以解决通用性的问题了。 顺便说一句,不要用clr,我们以前是clr,后来发现不能保证排序,不如sql xml,就放弃了。

--CLR 处理方式: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.IO; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate     (     Format.UserDefined,                 //使用UserDefined 序列化格式     IsInvariantToNulls = true,          //聚合是否与空值有关     IsInvariantToDuplicates = false,    //聚合是否与重复值有关     IsInvariantToOrder = false,         //聚合是否与顺序有关     MaxByteSize = 8000)                 //聚合实例的最大大小(以字节为单位) ] public class Concatenate : IBinarySerialize {     /// <summary>     /// 定义变量     /// </summary>     private StringBuilder intermediateResult;     /// <summary>     /// 初始化     /// </summary>     public void Init()     {         this.intermediateResult = new StringBuilder();     }     /// <summary>     /// 如果某一个字符不为空,用","追加     /// </summary>     /// <param name="value"></param>     public void Accumulate(SqlString value) //symbol     {         if (value.IsNull)         {             return;         }         this.intermediateResult.Append(value.Value).Append(',');     }     /// <summary>     /// 合并字符     /// </summary>     /// <param name="other"></param>     public void Merge(Concatenate other)     {         this.intermediateResult.Append(other.intermediateResult);     }     /// <summary>     /// 处理最后的","     /// </summary>     /// <returns></returns>     public SqlString Terminate()     {         string output = string.Empty;         //删除最后的","         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());     } } --测试: --创建创建包含类元数据和托管代码的托管应用程序模块,将其作为 SQL Server 实例中的对象。 CREATE ASSEMBLY SQLCLR_Aggregate FROM 'D:\IISWebRoot\VS2005\SQL_CLR\SQL_CLR\bin\Debug\SQL_CLR.dll' GO CREATE AGGREGATE SQLCLR_Aggregate (@input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME SQLCLR_Aggregate.Concatenate DECLARE @t TABLE(id int, value varchar(10)) INSERT @t SELECT 1, 'aa' UNION ALL SELECT 1, 'bb' UNION ALL SELECT 2, 'aaa' UNION ALL SELECT 2, 'bbb' UNION ALL SELECT 2, 'ccc' select id,values=dbo.SQLCLR_Aggregate([value]) from @t group by id drop AGGREGATE SQLCLR_Aggregate drop ASSEMBLY SQLCLR_Aggregate /* id          values --------   ----------- 1           aa,bb 2           aaa,bbb,ccc */ --我有一个问题: 如何把分隔开符","通过变量传递给拖管的CLR.

--我有一个问题: 如何把分隔开符","通过变量传递给拖管的CLR. 这个问题好象不好做,是 Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute定义好的,而这个东西,还是sealed.

合并与分拆的CLR, sql2005的示例中有: 在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中

收藏先!找时间看!

--sql 处理好,还可以把分隔符,用变量来处理.也可以支持排序, CLR 一直找不到该办法........

好贴~

MARK,谢谢邹老大

学习!

mark

留个记号

多谢,找了好久:)

mark

真的很郁悶, 用 Sybase ASA , 有一個 list 聚合函數, 真好,為什麼 SQL不提供呢. ASA: select id,list(values) as id_valuse from tablename group by id

mark

DB2中如何解决呢?

收藏

DB2没用过不知道,最简单的是 declare @a varchar(8000) select @a = @a + fieldA from tableB  这样

mark

断点

上一篇:“2006年中国首届杰出数据库工程师评选”,大家进来给邹建大哥加油啊! 200bugchen888]
下一篇:sql 2005 递归查询的奇怪问题 50jyxhz]

相关文章

相关评论