关于group by 的应用问题  

数据库内容为下面


 

写一SQL得出下面内容:


 

贴出SQL结果如下:(MySQL版本)

create table gosin_temp(rq varchar(10),shengfu nchar(1));

insert into gosin_temp values('2009-05-09','胜');
insert into gosin_temp values('2009-05-09','胜');
insert into gosin_temp values('2009-05-09','负');
insert into gosin_temp values('2009-05-09','负');
insert into gosin_temp values('2009-05-10','胜');
insert into gosin_temp values('2009-05-10','负');
insert into gosin_temp values('2009-05-10','负');

select * from gosin_temp;

得到结果的SQL:
select a1.rq,a1.胜,b1.负 from
(select a.rq, count(a.shengfu) 胜 from gosin_temp a where a.shengfu='胜' group by a.rq) a1,
(select b.rq, count(b.shengfu) 负 from gosin_temp b where b.shengfu='负' group by b.rq) b1
where a1.rq = b1.rq

类似的题目还有很多,如:

   胜 负
1 a b
2 b a
3 b a

要求写一SQL语句,输出如下结果:
   胜 负
a 1 2
b 2 1

其实都一样 只要熟悉使用group by 就不觉得难了。