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

无论是在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 的和（字符串相加）

  

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, 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  @t N

                 WHERE  id = A.id

                 FOR  XML AUTO

             ),  '<N value="' ,  ',' ),  '"/>' ,  '' ), 1, 1,  '' )

)N

  

/* --结果

id           values

----------- ----------------

1           aa,bb

2           aaa,bbb,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,1,1, '' ))

         SELECT  @s= ',' + CAST (@col2  as  varchar ),@col1_old=@col1

     END

     FETCH  tb  INTO  @col1,@col2

END

INSERT  @t  VALUES (@col1_old,STUFF(@s,1,1, '' ))

CLOSE  tb

DEALLOCATE  tb

--显示结果并删除测试数据

SELECT  *  FROM  @t

DROP  TABLE  tb

/* --结果

col1       col2

---------- -----------

a          1,2

b          1,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,1,1, '' ))

END

GO

  

--调用函数

SELECT  col1,col2=dbo.f_str(col1)  FROM  tb  GROUP  BY  col1

--删除测试

DROP  TABLE  tb

DROP  FUNCTION  f_str

/* --结果

col1       col2

---------- -----------

a          1,2

b          1,2,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

/* --更新处理后的临时表

col1       col2

---------- -------------

a          1

a          1,2

b          1

b          1,2

b          1,2,3

--*/

--得到最终结果

SELECT  col1,col2= MAX (col2)  FROM  #t  GROUP  BY  col1

/* --结果

col1       col2

---------- -----------

a          1,2

b          1,2,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

/* --结果

col1       col2      

---------- ----------

a          1,2

b          1,2

c          3

--*/

  

--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  tb a

GROUP  BY  col1

DROP  TABLE  tb

/* --结果

col1       col2

---------- ------------

a          1,2

b          1,2,3

c          3

--*/

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,2,3'  union  all

select  2,N '3,4'

Go

create  function  F_str(@cname nvarchar(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

  

id          cname

----------- ----------------------------------------------------------------------------------------------------

1           张三,李四,王五

2           王五,蔡六