按组分类求总值的sql语句(转)
有表tb, 如下：
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
------ -----------
1 aa,bb
------ -----------
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
即， 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
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
--*/
