declare @temp table(id int,name varchar(10),type char(1))
insert @temp values(1,'carry','a')
insert @temp values(2,'tom','b')
insert @temp values(3,'carry','c')
insert @temp values(4,'tom','c')
insert @temp values(5,'carry','c')
select name,
count(case when type = 'a' then 1 else null end) a,
count(case when type = 'b' then 1 else null end) b,
count(case when type = 'c' then 1 else null end) c
from @temp
group by namename a b c
---------- ----------- ----------- -----------
carry 1 0 2
tom 0 1 1(所影响的行数为 2 行)
insert @temp values(1,'carry','a')
insert @temp values(2,'tom','b')
insert @temp values(3,'carry','c')
insert @temp values(4,'tom','c')
insert @temp values(5,'carry','c')
select name,
count(case when type = 'a' then 1 else null end) a,
count(case when type = 'b' then 1 else null end) b,
count(case when type = 'c' then 1 else null end) c
from @temp
group by namename a b c
---------- ----------- ----------- -----------
carry 1 0 2
tom 0 1 1(所影响的行数为 2 行)
sum(case when type='a' then 1 else 0 end ) as a
sum(case when type='b' then 1 else 0 end ) as b
sum(case when type='c' then 1 else 0 end ) as c
from temp
group by name
as
select name
,a=sum(case type when 'a' then 1 else 0 end)
,b=sum(case type when 'b' then 1 else 0 end)
,c=sum(case type when 'c' then 1 else 0 end)
from temp group by name
set @s=''
select @s=@s+',['+type+']=sum(case type when '''+type+''' then 1 else 0 end)'
from(select distinct type from temp) a
exec('select name'+@s+' from temp group by name')
as
select name
,a=sum(case type when 'a' then 1 else 0 end)
,b=sum(case type when 'b' then 1 else 0 end)
,c=sum(case type when 'c' then 1 else 0 end)
from temp group by name
union all
select '总计'
,a=sum(case type when 'a' then 1 else 0 end)
,b=sum(case type when 'b' then 1 else 0 end)
,c=sum(case type when 'c' then 1 else 0 end)
from temp
insert @temp values(1,'carry','a')
insert @temp values(2,'tom','b')
insert @temp values(3,'carry','c')
insert @temp values(4,'tom','c')
insert @temp values(5,'carry','c')
select name,
count(case when type = 'a' then 1 else null end) a,
count(case when type = 'b' then 1 else null end) b,
count(case when type = 'c' then 1 else null end) c
from @temp
group by name
union all
select '总计',
count(case when type = 'a' then 1 else null end) a,
count(case when type = 'b' then 1 else null end) b,
count(case when type = 'c' then 1 else null end) c
from @temp
name a b c
---------- ----------- ----------- -----------
carry 1 0 2
tom 0 1 1
总计 1 1 3(所影响的行数为 3 行)