Oracle decode case when 行转列 分组查询

1.行转列查询，使用 decode或者case when

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 Test_Decode(rq varchar(10),shengfu nchar(1));
insert into Test_Decode values('2005-05-09','胜');
insert into Test_Decode values('2005-05-09','胜');
insert into Test_Decode values('2005-05-09','负');
insert into Test_Decode values('2005-05-09','负');
insert into Test_Decode values('2005-05-10','胜');
insert into Test_Decode values('2005-05-10','负');
insert into Test_Decode values('2005-05-10','负');

--1 使用 decode 实现
--1.1 查看数据
select rq,shengfu from test_decode;
--1.2 进一步分析数据
select rq,decode(shengfu,'胜',1,0) sheng,decode(shengfu,'负',1,0) fu from test_decode;
--1.3 最后得到想要的数据
select rq,sum(decode(shengfu,'胜',1,0)) 胜,sum(decode(shengfu,'负',1,0)) 负 from test_decode group by rq order by 1;

--2 使用 case when 实现
--2.1 方法1 case when shengfu='胜'
select rq,
       sum(case when shengfu='胜' then 1 else 0 end) 胜 ,
       sum(case when shengfu='负' then 1 else 0 end) 负 
       from test_decode group by rq order by 1;

--2.2 方法2 case shengfu when to_nchar('胜')
select rq,
       sum(case shengfu when to_nchar('胜') then 1 else 0 end) 胜 ,
       sum(case shengfu when to_nchar('负') then 1 else 0 end) 负 
       from test_decode group by rq order by 1;

--3 使用子查询
select tab1.rq,tab1.胜,tab2.负 from 
       ( select rq,count(*) 胜 from Test_Decode where shengfu = to_nchar('胜') group by rq ) tab1 join
       ( select rq,count(*) 负 from Test_Decode where shengfu = to_nchar('负') group by rq ) tab2 on (tab1.rq = tab2.rq)
       order by 1;

2.查出不同年龄段的学生的人数
create table student(
       id number(4) primary key,
       name varchar2(90),
       age number(4)
);

select id,name,age from student;
insert into student values (1,'a',10);
insert into student values (2,'b',20);
insert into student values (3,'c',30);
insert into student values (4,'d',40);
insert into student values (5,'e',50);
insert into student values (6,'f',60);
insert into student values (7,'g',70);
insert into student values (8,'h',80);
insert into student values (9,'i',90);
insert into student values (10,'g',100);
insert into student values (11,'g',66);
insert into student values (12,'g',77);
insert into student values (13,'g',55);
--查询出这样的数据
年龄段，人数
   < 30  数目
30 - 60  数目
60 >     数目

select name,age from student;
--使用两次case when判断，group by也要使用case when
select ( case when age <= 30 then '   < 30'
            when age > 30 and age <= 60 then '30 - 60'
            when age > 60 then '60 > '
       end ) 年龄段,
       sum( case when age <= 30 then 1
            when age > 30 and age <= 60 then 1
            when age > 60 then 1
       end ) 人数
       from student 
       group by 
           case when age <= 30 then '   < 30'
                when age > 30 and age <= 60 then '30 - 60'
                when age > 60 then '60 > '
           end ;

--加一层嵌套使SQL更加简洁
select age_area 年龄段,sum(age_num) 人数 from (
    select  case when age <= 30 then '   < 30'
                when age > 30 and age <= 60 then '30 - 60'
                when age > 60 then '60 > '
           end  age_area,
           case when age <= 30 then 1
                when age > 30 and age <= 60 then 1
                when age > 60 then 1
           end age_num
           from student 
) group by age_area;