create table worldcup_vs_team
(
 round_level number(9),
 group_id varchar2(100),
 group_rank number(4),
 group_order number(4),
 team_name varchar2(30));

delete from worldcup_vs_team where round_level<>16;
commit;
select * from worldcup_vs_team for update;
/*select ASCII('B')-ASCII('A') from dual;*/

create or replace view worldcup_vs_team_level_8 as
select rownum rn,aa.*
from
(select a.group_id_rank vs_a,
       team_name_A,
       b.group_id_rank vs_b,
       team_name_B
  from (select group_id || group_rank group_id_rank,
               group_order group_order,
               mod(group_rank, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_A
          from worldcup_vs_team
         where mod(group_order, 2) = 1 and round_level=16) a,
       (select group_id || group_rank group_id_rank,
               group_order - 1 group_order,
               mod(group_rank + 1, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_B
          from worldcup_vs_team
         where mod(group_order, 2) = 0 and round_level=16) b
 where a.group_order = b.group_order
   and a.group_rk = b.group_rk
 ORDER BY 1) aa;
---------------
select * from worldcup_vs_team_level_8;
---------------
create or replace view worldcup_vs_team_level_4 as
select rownum rn,aa.*
from
(select a.group_id_rank vs_a,
       team_name_A,
       b.group_id_rank vs_b,
       team_name_B
  from (select group_id || group_rank group_id_rank,
               group_order group_order,
               mod(group_rank, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_A
          from worldcup_vs_team
         where mod(group_order, 2) = 1 and round_level=8) a,
       (select group_id || group_rank group_id_rank,
               group_order - 1 group_order,
               mod(group_rank + 1, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_B
          from worldcup_vs_team
         where mod(group_order, 2) = 0 and round_level=8) b
 where a.group_order = b.group_order
   and a.group_rk = b.group_rk
 ORDER BY 1) aa;
---------------
select * from worldcup_vs_team_level_4;
---------------
create or replace view worldcup_vs_team_level_2 as
select rownum rn,aa.*
from
(select a.group_id_rank vs_a,
       team_name_A,
       b.group_id_rank vs_b,
       team_name_B
  from (select group_id || group_rank group_id_rank,
               group_order group_order,
               mod(group_rank, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_A
          from worldcup_vs_team
         where mod(group_order, 2) = 1 and round_level=4) a,
       (select group_id || group_rank group_id_rank,
               group_order - 1 group_order,
               mod(group_rank + 1, 2) group_rk,
               group_id,
               group_rank,
               team_name team_name_B
          from worldcup_vs_team
         where mod(group_order, 2) = 0 and round_level=4) b
 where a.group_order = b.group_order
   and a.group_rk = b.group_rk
 ORDER BY 1) aa;
---------------
select * from worldcup_vs_team_level_2;
---------------
create or replace view worldcup_vs_team_final as
select rownum rn,VS_A,team_name_A,VS_B,team_name_B
from
(select group_rank,
       group_order,
       decode(group_rank, 1, group_id || group_rank) VS_A,
       decode(group_rank, 1, team_name) team_name_A/*,
       decode(group_rank, 2, group_id || group_rank) VS_B,
       decode(group_rank, 2, team_name) team_name_B*/
  from worldcup_vs_team
 where round_level = 2 and group_rank=1
order by 1) a,
(select group_rank,
       group_order,
       /*decode(group_rank, 1, group_id || group_rank) VS_A,
       decode(group_rank, 1, team_name) team_name_A,*/
       decode(group_rank, 2, group_id || group_rank) VS_B,
       decode(group_rank, 2, team_name) team_name_B
  from worldcup_vs_team
 where round_level = 2 and group_rank=2
order by 1) b
where a.group_order=b.group_order;
---------------
select * from worldcup_vs_team_final;
---------------
--Ãû´Î
create or replace view worldcup_final_order as
select rownum rn,team_name from
(select a.group_rank,a.group_order,a.team_name from worldcup_vs_team a
where a.round_level=1
order by 1,2);

select * from worldcup_final_order;