select date,sum(case name when 'Mike' then 1 else 0 end) as [Mike 出现次数]
,sum(case name when 'John' then 1 else 0 end) as [John 出现次数]
,sum(case name when 'Alex' then 1 else 0 end) as [Alex 出现次数]
from 你的表
group by date
,sum(case name when 'John' then 1 else 0 end) as [John 出现次数]
,sum(case name when 'Alex' then 1 else 0 end) as [Alex 出现次数]
from 你的表
group by date
,(case when 'John' then count(Name) end) as John出现次数,
,(case when 'Alex' then count(Name) end) as Alex出现次数 from table group by date
出于好奇,我想再问一句: 如果不知道Name里倒底有多少可能性的话,怎么把全部的人的出现次数查出来呢?
set @sql='select date'
declare #aa cursor for select distinct name from 你的表
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
set @sql=@sql+char(13)+',sum(case name when '''+@name
+''' then 1 else 0 end) as ['+@name+' 出现次数]'
fetch next from #aa into @name
end
close #aa
deallocate #aa
set @sql=@sql+char(13)+'from 你的表 group by date'
exec (@sql)