一道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','负');
commit;
select rq 时间, max(sheng) 胜, max(fu)负
from (
select rq, decode(shengfu,'胜', all_num,'') sheng,
decode(shengfu,'负',all_num,'') fu
from (
select rq,shengfu,count(shengfu) all_num
from tmp group by rq, shengfu )
) group by rq order by rq
或者可以这样：
select rq,sum(to_number(case when shengfu='胜' then replace(shengfu,'胜','1') else '0' end)) 胜,
sum(to_number(case when shengfu='负' then replace(shengfu,'负','1') else '0' end)) 负
from tmp group by rq
注意replace的用法
换一下： 如果数据库中胜变为1, 负为0
update tmp set shengfu=( case when shengfu='胜' then 1 when shengfu='负' then 0 end);
commit;
select rq,count(case when shengfu=1 then 1 end ) 胜,
count(case when shengfu=0 then 0 end ) 负
from tmp group by rq
更为简单的方法：
select rq, sum(decode(shengfu,'胜',1 )) 胜, sum(decode(shengfu,'负',1)) 负 from tmp group by rq order by rq;