急--sqlserver查询问题

来源：百度知道 编辑：UC知道 时间：2024/07/02 19:21:56
一道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 rq,sum(case when shengfu='胜' then 1 end) as 胜 ，sum(case when shengfu='负' then 1 end) as 负 from #tmp group by rq

少有的聪明的提问题者，你一定会得到最准确的答复
SELECT RQ,SUM(CASE WHEN SHENGFU='胜' then 1 else 0 end) as 胜,
SUM(CASE WHEN SHENGFU='负' then 1 else 0 end) as 负
from #tmp group by rq

select rq,sum(cnt1),sum(cnt2) from
(select rq,count(*) cnt1,0 cnt2 from #tmp where shengfu='胜' group by rq,shengfu
union
select rq,0,count(*) from #tmp where shengfu='负' group by rq,shengfu) a
group by rq

学习