我想写一条SELECT 语句。
表内容如下：
A B C
1 300 a1
1 200 a2
2 500 b1
2 300 b2
我要得到如下内容：
A B C
1 500 a1,a2
2 800 b1,b2
按A分组，B是求和，C是内容连接
SQL
------解决方案--------------------
create table dri
(A int, B int, C varchar(10))
insert into dri
select 1, 300, 'a1' union all
select 1, 200, 'a2' union all
select 2, 500, 'b1' union all
select 2, 300, 'b2'
select a.A,
sum(a.B) 'B',
stuff((select ','+C
from dri b
where b.A=a.A
for xml path('')),1,1,'') 'C'
from dri a
group by a.A
/*
A B C
----------- ----------- ------------
1 500 a1,a2
2 800 b1,b2
(2 row(s) affected)
*/
------解决方案--------------------
if exists(select * from sysobjects where name ='TT')
drop table TT
go
create table TT
(
A int,
B int,
C varchar(10)
)
insert into TT
select 1,300, 'a1' union all
select 1,200, 'a2' union all
select 2,500, 'b1' union all
select 2,300, 'b2'
go
select A,sum(isnull(B,0))B,
stuff((select ','+C from TT x where x.A=y.A for xml path('')),1,1,'')C
from TT y group by A
------解决方案--------------------
SELECT A,SUM(B) B, C = STUFF((SELECT ',' + CAST(C AS NVARCHAR(MAX))
FROM dri T1 WHERE (A = T2.A)
FOR XML PATH ('')),1,1,'')
FROM dri T2
GROUP BY A
运行结果：
------解决方案--------------------