- SQL code问题描述:
- 无论是在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 王五,蔡六
- (2 個資料列受到影響)
SQL经典应用(一)
最新推荐文章于 2024-05-31 15:50:21 发布