求SQL语句

daring1981 2010-07-15 03:31:19

表1
10060001 a
10060002 b
10060003 a
10060004 c
10060005 c
10060006 c

查询结果为：
a 2 10060001,10060003
b 1 10060002
c 3 10060004,10060005，1006000
sql语句如何写？

...全文

60 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

hanks_gao 2010-07-15

打赏
举报

MARK!!!

shixixi1987 2010-07-15

打赏
举报

select zm,count(zm),tutor_name= stuff((select ',' + [name]
from tb1 t where zm = tb1.zm for xml path('')) , 1 , 1 , '')
from tb1 group by zm
go

shixixi1987 2010-07-15

打赏
举报

if exists(select * from sysobjects where [name]='tb1')
drop table tb1
go
create table tb1
(
[name] varchar(20),
zm varchar(10)
)
go
insert into tb1([name],zm)
(
select '10060001','a' union
select '10060002','b' union
select '10060003','a' union
select '10060004','c' union
select '10060005','c' union
select '10060006','c'
)
go
select * from tb1
go
select zm,tutor_name= stuff((select ',' + [name]
from tb1 t where zm = tb1.zm for xml path('')) , 1 , 1 , '')
from tb1 group by zm
go

zhengtw 2010-07-15

打赏
举报

SELECT B, COUNT(*), STUFF((SELECT ',' + A FROM TB1 WHERE B = T.B FOR XML PATH('')), 1, 1, '')

FROM TB1 T

GROUP BY B

rucypli 2010-07-15

打赏
举报

create function test(@temp varchar(10))
returns varchar(1000)
as
begin
declare @t varchar(1000)
set @t =''
select @t= @t+col1
from tb
where colb=@temp
return (@t)
end

select col2,count(col1),test(col2)
from tb
group by col2

daring1981 2010-07-15