问题描述: 无论是在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. 新的解决方法(适用于2005及以后版本) -- 示例数据 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 行受影响) --*/CSDN 社区帖子地址 附: 合并与分拆的CLR, sql2005的示例中有: 在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
create function f_1(@id varchar(200)) returns varchar(8000) as begin declare @str varchar(8000) select @str='' select @str=','+@str+city_nm from tb_b where charindex(','+rtrim(city_id)+',',','+@id+',')>0 return @str end go select name,city_id,dbo.f_1(city_id) as city_nm from tb_a a go
create function f_1(@id varchar(200)) returns varchar(8000) as begin declare @str varchar(8000) select @str='' select @str=','+@str+city_nm from tb_b where charindex(','+rtrim(city_id)+',',','+@id+',')>0 return stuff(@str,1,1,'') end go
--> 生成测试数据: @TB_A DECLARE @TB_A TABLE (NAME VARCHAR(3),CITY_ID VARCHAR(5),CITY_NM VARCHAR(1)) INSERT INTO @TB_A SELECT 'AAA','2,3,4','?' UNION ALL SELECT 'BBB','1,5','?' UNION ALL SELECT 'CCC','2,4','?'
--> 生成测试数据: @TB_B DECLARE @TB_B TABLE (CITY_ID INT,CITY_NM VARCHAR(4)) INSERT INTO @TB_B SELECT 1,'北京' UNION ALL SELECT 2,'上海' UNION ALL SELECT 3,'浙江' UNION ALL SELECT 4,'江苏' UNION ALL SELECT 5,'安徽'--SQL查询如下:SELECT NAME, CITY_ID, CAST(STUFF( (SELECT ','+CITY_NM AS [text()] FROM @TB_B WHERE CHARINDEX(','+CAST(CITY_ID AS VARCHAR(5))+',', ','+A.CITY_ID+',')>0 FOR XML PATH('') ),1,1,'') AS VARCHAR(30)) AS CITY_NM FROM @TB_A AS A/* NAME CITY_ID CITY_NM ---- ------- ------------------------------ AAA 2,3,4 上海,浙江,江苏 BBB 1,5 北京,安徽 CCC 2,4 上海,江苏(3 行受影响) */
sql2000不也有for xml可用的吗?
select name,tb_b.city_nm into tb2 from tb_a,tb_b where tb_a.city_id like '%'+cast(tb_b.city_id as varchar)+'%'CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+city_nm FROM tb2 WHERE name=@col1 RETURN(STUFF(@re,1,1,'')) END GOselect name,city_nm=dbo.f_str(name) from tb2 group by nameAAA 上海,浙江,江苏 BBB 北京,安徽 CCC 上海,江苏
无论是在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. 新的解决方法(适用于2005及以后版本)
-- 示例数据
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 行受影响)
--*/CSDN 社区帖子地址 附: 合并与分拆的CLR, sql2005的示例中有:
在安装sql 2005的示例后,默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
create function f_1(@id varchar(200))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=''
select @str=','+@str+city_nm from tb_b where charindex(','+rtrim(city_id)+',',','+@id+',')>0
return @str
end
go
select name,city_id,dbo.f_1(city_id) as city_nm
from tb_a a
go
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=''
select @str=','+@str+city_nm from tb_b where charindex(','+rtrim(city_id)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
-- Author: liangCK 小梁
-- Date : 2008-11-21 12:18:27
---------------------------------
--> 生成测试数据: @TB_A
DECLARE @TB_A TABLE (NAME VARCHAR(3),CITY_ID VARCHAR(5),CITY_NM VARCHAR(1))
INSERT INTO @TB_A
SELECT 'AAA','2,3,4','?' UNION ALL
SELECT 'BBB','1,5','?' UNION ALL
SELECT 'CCC','2,4','?'
--> 生成测试数据: @TB_B
DECLARE @TB_B TABLE (CITY_ID INT,CITY_NM VARCHAR(4))
INSERT INTO @TB_B
SELECT 1,'北京' UNION ALL
SELECT 2,'上海' UNION ALL
SELECT 3,'浙江' UNION ALL
SELECT 4,'江苏' UNION ALL
SELECT 5,'安徽'--SQL查询如下:SELECT
NAME,
CITY_ID,
CAST(STUFF(
(SELECT ','+CITY_NM AS [text()]
FROM @TB_B
WHERE CHARINDEX(','+CAST(CITY_ID AS VARCHAR(5))+',',
','+A.CITY_ID+',')>0
FOR XML PATH('')
),1,1,'')
AS VARCHAR(30)) AS CITY_NM
FROM @TB_A AS A/*
NAME CITY_ID CITY_NM
---- ------- ------------------------------
AAA 2,3,4 上海,浙江,江苏
BBB 1,5 北京,安徽
CCC 2,4 上海,江苏(3 行受影响)
*/
from tb_a,tb_b
where tb_a.city_id like '%'+cast(tb_b.city_id as varchar)+'%'CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+city_nm
FROM tb2
WHERE name=@col1
RETURN(STUFF(@re,1,1,''))
END
GOselect name,city_nm=dbo.f_str(name)
from tb2
group by nameAAA 上海,浙江,江苏
BBB 北京,安徽
CCC 上海,江苏