关于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 就不觉得难了。