导航

[SQL]SQL类似统计功能的sql文

Posted on 2014-03-24 20:24  beeone  阅读(286)  评论(0编辑  收藏  举报
declare @t table(name varchar(20),type int)
insert into @t
select 'a',0
union all select 'b',0
union all select 'fb',1
union all select 'fbf',2
union all select 'fdfs',1





if object_id('test1') is not null
drop table test1
create table test1(
name varchar(20),
type int)
insert into test1
select 'a',0
union all select 'b',0
union all select 'fb',1
union all select 'fbf',2
union all select 'fdfs',1

select * from test1

create function ss()
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+ name from test1 
set @sql=stuff(@sql,1,1,' ')
return @sql
end

select dbo.ss(),count_0=(select count(1) from test1 where type=0),
count_1=(select count(1) from test1 where type=1),
count_2=(select count(1) from test1 where type=2)
/*
我想要的结果是这样的
    name         count_0   count_1   count_2  
a,b,fb,fbf,fdfs       2       2         1

*/