日期:2014-05-18  浏览次数:20324 次

求sql写法
某表tb,有两列a,b其值如下
a b
str1 5
str2 6 
str2 3
str3 7
str3 2


想要效果a列字符串连接起来,中间逗号隔开,b列数值相加
结果如下

  a b
str1,str2,str2,str3,str3 23

------解决方案--------------------
SQL code

--试试
declare @s varchar(200) 
set @s=''
select @s=@s+a+',' from tb
select left(@s,len(@s)-1) as a,sum(b) as b from tb

------解决方案--------------------
SQL code


--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([a] varchar(4),[b] int)
insert [tbl]
select 'str1',5 union all
select 'str2',6 union all
select 'str2',3 union all
select 'str3',7 union all
select 'str3',2

declare @str varchar(1000)
set @str=''
select @str=@str+','+a from tbl
select right(@str,len(@str)-1)+' '+LTRIM(sum(b)) from tbl
print @str

(无列名)
str1,str2,str2,str3,str3 23

------解决方案--------------------
SQL code

declare @str varchar(1000)
set @str=''
select @str=@str+','+a from tbl
select SUBSTRING(@str,2,len(@str))+' '+LTRIM(sum(b)) from tbl
print @str

------解决方案--------------------
SQL code
create table tb(a varchar(10),b int)
insert into tb values('str1',5)
insert into tb values('str2',6)
insert into tb values('str2',3)
insert into tb values('str3',7)
insert into tb values('str3',2)
go

select a = stuff((select ',' + a from tb t for xml path('')) , 1 , 1 , '') , sum(b) b
from tb

drop table tb

/*
a                         b
------------------------  ---
str1,str2,str2,str3,str3  23

(1 行受影响)

------解决方案--------------------
select @str=isnull(@str,0)+','+a from tbl