经典老问题,字符串聚合函数求新解
[code=SQL]问题描述:
无论是在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_str1(@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
-- 调用函数
-- 2. 新的解决方法(适用于2005及以后版本)
-- 示例数据
Create TABLE #tb(id int, value varchar(10))
INSERT #tb 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, value=dbo.f_str1(id)
FROM tb
GROUP BY id
-- 查询处理
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 行受影响)
--*/
[/code]
以上是传统解决办法,现在能不能用临时表和游标实现这个效果,我们老大给我的思路是用游标获取values的值,创建一个id,values的临时表
最后将获取的值写入到临时表 ,摸索了一个上午,终于搞定了。现将代码共享
---利用游标聚合字符串
Create TABLE #tb(id int, value varchar(10))
INSERT #tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
select distinct id into #ttbb from #tb
select * from #ttbb drop table #ttbb
alter table #ttbb add value varchar(200)
delete from #ttbb
DECLARE @ID int
declare @Value varchar(8000)
DECLARE GetValue CURSOR
FOR
select ID,Value from #tb order by ID
OPEN GetValue
FETCH NEXT FROM GetValue INTO @ID,@value
WHILE @@FETCH_STATUS = 0
BEGIN
if(@ID!=0)
begin
if(EXISTS(select * from #ttbb where ID=@ID ))
begin
update #ttbb set ID=b.ID,value=a.value+','+b.value from #tb a left join #ttbb b on a.ID=b.ID
where a.ID=@ID and a.value=@value
end
else
begin
insert #ttbb(ID,value)
select ID,value
from #tb where ID=@ID and value=@value
end
end
FETCH NEXT FROM GetValue INTO @ID,@value
END
CLOSE GetValue
DEALLOCATE GetValue
select * from #ttbb