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

[tata_1980]

表内容：
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

--------------多谢!!!!

[Rotaxe]

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 rq, sum(case when shengfu='胜' then 1 else 0 end)'胜' , sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq

[talantlee]

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 N.rq,N.勝,M.負 from (select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq

---純隨搶點分

[hj0849cn]

select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001

[guchengxilu]

SELECT 字段B ='胜利',字段B='负'INTO 新表 FROM 表 WHERE 字段A='2005-05-09' AND 字段A='2005-05-10'

SLECT * FROM 新表
这样行吗？~^_^
千万不要扔鸡蛋

[xluzhong]

select [日期]=convert(char(10),日期,120),
[胜]=sum(case when [胜负]='胜' then 1 else 0 end,
[负]=sum(case when [胜负]='负' then 1 else 0 end
from tablename
group by convert(char(10),日期,120)
order by convert(char(10),日期,120)

[tdtjjiao]

select [日期]=convert(char(10),日期,120),
[胜]=sum(case when [胜负]='胜' then 1 else 0 end,
[负]=sum(case when [胜负]='负' then 1 else 0 end
from tablename
group by convert(char(10),日期,120)
order by convert(char(10),日期,120)

[rfq]

select rq,sum(case shengfu when '胜' then 1 else 0 end) as 胜,sum(case shengfu when '负' then 1 else 0 end) as 负 from
表 group by rq

select rq ,(select count(*) from 表 b where a.rq=b.rq and b.shengfu='胜') as 胜 ,(select count(*) from 表 c where a.rq=c.rq and c.shengfu='负') as 负 from
表 a group by rq