广告合作QQ：76874919

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

设计模式 2020-05-16
无论是在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
@H_438_404@ WHERE id=A.id
FOR XMLAUTO
), '<Nvalue="' , ',' ), '"/>' , '' ), '' )
)N
/* --结果
id values
---------------------------
1aa,bb
2aaa,ccc
(2行受影响)
--*/
--各种字符串分函数
--3.3.1使用游标法进行字符串合并处理的示例。
@H_11_502@ --处理的数据
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
@H_110_1404@ -----------------------
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
@H_183_1502@
/*==============================================*/
--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王五,蔡六

相关文章

适配器模式-让不兼容的接口得以适配
适配器模式将一个类的接口转换成客户期望的另一个接口，使得原本接口不兼容的类可以相互合作。
策略模式-定义一个算法族
策略模式定义了一系列算法族，并封装在类中，它们之间可以互相替换，此模式让算法的变化独立于使用算法...
设计模式之高质量代码
设计模式讲的是如何编写可扩展、可维护、可读的高质量代码，它是针对软件开发中经常遇到的一些设计问题...
模板方法模式-封装一套算法流程
模板方法模式在一个方法中定义一个算法的骨架，而将一些步骤延迟到子类中，使得子类可以在不改变算法结...
迭代器模式-统一集合的遍历方式
迭代器模式提供了一种方法，用于遍历集合对象中的元素，而又不暴露其内部的细节。
外观模式-简化子系统的复杂性
外观模式又叫门面模式，它提供了一个统一的（高层）接口，用来访问子系统中的一群接口，使得子系统更容...