原始表为:
UserID Cost Memo
1 20 abc
1 30 efg
. . .
. . .
. . .
经过汇总后的效果
UserID Cost Memo
1 50 abc,efg. . .
. . .
. . .
如何实现?
在应用程序中实现好还是在SQL中实现好?该表每月生成一次,数据量大概每月300条
UserID Cost Memo
1 20 abc
1 30 efg
. . .
. . .
. . .
经过汇总后的效果
UserID Cost Memo
1 50 abc,efg. . .
. . .
. . .
如何实现?
在应用程序中实现好还是在SQL中实现好?该表每月生成一次,数据量大概每月300条
insert into mytb select
1 , 20 , 'abc' union select
1 , 30 , 'efg'create function get_str(@userid int )
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''select @str=@str+','+memo
from mytb
where userid=@userid
set @str=stuff(@str,1,1,'')
return @str
endselect userid,sum(cost) as cost,dbo.get_str(1) as memo
from mytb
where userid=1
group by userid
userid cost memo
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 50 abc,efg(所影响的行数为 1 行)
我觉得在服务器端用sql执行好,通过程序速度不会好过直接sql.可以看相关介绍
---------to: liangpei2008
create table mytb(UserID int,Cost int,Memo varchar(8000))
insert into mytb select 1,20, 'abc'
union all select 1,30, 'efg'
gocreate function get_str(@userid int )
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+memo
from mytb
where userid=@userid
set @str=stuff(@str,1,1,'')
return @str
end
goselect userid,sum(cost) as cost,dbo.get_str(1) as memo
from mytb
where userid=1
group by useriddrop table mytb
drop function get_str
go