解决聚合函数的问题（一）

无论是在sql2000，还是在sql2005中,都没有提供字符串的聚合函数， 所以，当我们在处理下列要求时，会比较麻烦： 有表tb,如下： idvalue ----------- 1aa 1bb 2aaa 2bbb 2ccc 需要得到结果： id values ----------------- 1aa,bb 2aaa,bbb,ccc 即， group by id,求value的和（字符串相加） 1.旧的解决方法 --1.创建处理函数 CREATE FUNCTION dbo.f_str(@id int ) RETURNS varchar (8000) AS BEGIN DECLARE @r varchar (8000) SET @r= '' SELECT @r=@r+ ',' +value FROM tb WHERE id=@id RETURN STUFF(@r,1, '' ) END GO --调用函数 SELECt id, values =dbo.f_str(id) FROM tb GROUP BY id --2.新的解决方法 --示例数据 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 @tN WHERE id=A.id FOR XMLAUTO ), '<Nvalue="' , ',' ), '"/>' , '' ), '' ) )N /* --结果 id values --------------------------- 1aa,bb 2aaa,ccc (2行受影响) --*/ --各种字符串分函数 --3.3.1使用游标法进行字符串合并处理的示例。 --处理的数据 CREATE TABLE tb(col1 varchar (10),col2 int ) INSERT tb SELECT 'a' ,1 UNION ALL SELECT 'a' ,2 UNION ALL SELECT 'b' ,1 UNION ALL SELECT 'b' ,2 UNION ALL SELECT 'b' ,3 --合并处理 --定义结果集表变量 DECLARE @t TABLE (col1 varchar (10),col2 varchar (100)) --定义游标并进行合并处理 DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar (10),@col1 varchar (10),@col2 int ,@s varchar (100) OPEN tb FETCH tb INTO @col1,@col2 SELECT @col1_old=@col1,@s= '' WHILE@@FETCH_STATUS=0 BEGIN IF@col1=@col1_old SELECT @s=@s+ ',' + CAST (@col2 as varchar ) ELSE BEGIN INSERT @t VALUES (@col1_old,STUFF(@s, '' )) SELECT @s= ',' + CAST (@col2 as varchar ),@col1_old=@col1 END FETCH tb INTO @col1,@col2 END INSERT @t VALUES (@col1_old, '' )) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT * FROM @t DROP TABLE tb /* --结果 col1col2 --------------------- a1,2 b1,2,3 --*/ GO /*==============================================*/ --3.3.2使用用户定义函数，配合SELECT处理完成字符串合并处理的示例 --处理的数据 CREATE TABLE tb(col1 varchar (10),col2 int ) INSERT tb SELECT 'a' ,1 UNION ALL SELECT 'a' ,2 UNION ALL SELECT 'b' ,1 UNION ALL SELECT 'b' ,2 UNION ALL SELECT 'b' ,3 GO --合并处理函数 CREATE FUNCTION dbo.f_str(@col1 varchar (10)) RETURNS varchar (100) AS BEGIN DECLARE @re varchar (100) SET @re= '' SELECT @re=@re+ ',' + CAST (col2 as varchar ) FROM tb WHERE col1=@col1 RETURN (STUFF(@re, '' )) END GO --调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1 --删除测试 DROP TABLE tb DROP FUNCTION f_str /* --结果 col1col2 --------------------- a1,2 b1,3 --*/ GO /*==============================================*/ --3.3.3使用临时表实现字符串合并处理的示例 --处理的数据 CREATE TABLE tb(col1 varchar (10),col2 int ) INSERT tb SELECT 'a' ,1 UNION ALL SELECT 'a' ,2 UNION ALL SELECT 'b' ,1 UNION ALL SELECT 'b' ,2 UNION ALL SELECT 'b' ,3 --合并处理 SELECT col1,col2= CAST (col2 as varchar (100)) INTO #t FROM tb ORDER BY col1,col2 DECLARE @col1 varchar (10),@col2 varchar (100) UPDATE #t SET @col2= CASE WHEN @col1=col1 THEN @col2+ ',' +col2 ELSE col2 END , @col1=col1, col2=@col2 SELECT * FROM #t /* --更新处理后的临时表 col1col2 ----------------------- a1 a1,2 b1 b1,2 b1,3 --*/ --得到最终结果 SELECT col1,col2= MAX (col2) FROM #t GROUP BY col1 /* --结果 col1col2 --------------------- a1,2 b1,3 --*/ --删除测试 DROP TABLE tb,#t GO /*==============================================*/ --3.3.4.1每组<=2条记录的合并 --处理的数据 CREATE TABLE tb(col1 varchar (10),col2 int ) INSERT tb SELECT 'a' ,1 UNION ALL SELECT 'a' ,2 UNION ALL SELECT 'b' ,1 UNION ALL SELECT 'b' ,2 UNION ALL SELECT 'c' ,3 --合并处理 SELECT col1, col2= CAST ( MIN (col2) as varchar ) + CASE WHEN COUNT (*)=1 THEN '' ELSE ',' + CAST ( MAX (col2) as varchar ) END FROM tb GROUP BY col1 DROP TABLE tb /* --结果 col1col2 -------------------- a1,2 b1,2 c3 --*/ --3.3.4.2每组<=3条记录的合并 --处理的数据 CREATE TABLE tb(col1 varchar (10),col2 int ) INSERT tb SELECT 'a' ,1 UNION ALL SELECT 'a' ,2 UNION ALL SELECT 'b' ,1 UNION ALL SELECT 'b' ,2 UNION ALL SELECT 'b' ,3 UNION ALL SELECT 'c' ,3 --合并处理 SELECT col1, col2= CAST ( MIN (col2) as varchar ) + CASE WHEN COUNT (*)=3 THEN ',' + CAST (( SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN ( MAX (a.col2), MIN (a.col2))) as varchar ) ELSE '' END + CASE WHEN COUNT (*)>=2 THEN ',' + CAST ( MAX (col2) as varchar ) ELSE '' END FROM tba GROUP BY col1 DROP TABLE tb /* --结果 col1col2 ---------------------- a1,2 b1,3 c3 --*/ GO if not object_id( 'A' ) is null drop table A Go Create table A([id] int ,[cname]nvarchar(2)) Insert A select 1,N '张三' union all select 2,N '李四' union all select 3,N '王五' union all select 4,N '蔡六' Go -->--> if not object_id( 'B' ) is null drop table B Go Create table B([id] int ,[cname]nvarchar(5)) Insert B select 1,N '1,3' union all select 2,N '3,4' Go create function F_str(@cnamenvarchar(100)) returns nvarchar(100) as begin select @cname= replace (@cname,ID,[cname]) from A where patindex( '%,' +rtrim(ID)+ ',%' ,' +@cname+ ',' )>0 return @cname end go select [id],dbo.F_str([cname])[cname] from B idcname --------------------------------------------------------------------------------------------------------------- 1张三,李四,王五 2王五,蔡六

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!