一道SQL语句面试题，关于group by

一道SQL语句面试题，关于group by
表内容：2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

            胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------

create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')

select * from #tmp
select rq, shengfu from #tmp group by rq,shengfu
select rq, shengfu from #tmp group by shengfu,rq
select rq, shengfu,sum(shengfu) from #tmp group by shengfu,rq

1)select rq, 

sum(case when shengfu='胜' then 1 else 0 end)'胜',

sum(case when shengfu='负' then 1 else 0 end)'负' 

from #tmp 

group by rq

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zh520/archive/2009/04/17/4087781.aspx