矩阵转化

一道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;