用SQL分析世界杯資料

建立資料（2014巴西世界杯資料）

create table teams (id varchar2(3) primary key , name varchar2(20) not null ) ;
create table match_results (team1 varchar2(3) references teams(id) not null, team1_goals integer not null ,
team2 varchar2(3) references teams(id) not null, team2_goals integer not null , check (team1 != team2 and team1_goals >=0 and team2_goals >=0 ) ) ;
 
insert into teams values ('A1' , '巴西') ;
insert into teams values ('A2' , '喀麥隆') ;
insert into teams values ('A3' , '墨西哥' ) ;
insert into teams values ('A4' , '克羅地亞') ;
insert into teams values ('B1' , '西班牙') ;
insert into teams values ('B2' , '智利' ) ;
insert into teams values ('B3' , '澳洲' ) ;
insert into teams values ('B4' , '荷蘭' ) ;
insert into teams values ('C1' ,'哥倫比亞') ;
insert into teams values ('C2' ,'科特迪瓦') ;
insert into teams values ('C3' ,'日本') ;
insert into teams values ('C4' ,'希臘') ;
insert into teams values ('D1' ,'烏拉圭') ;
insert into teams values ('D2' ,'英格蘭') ;
insert into teams values ('D3' ,'哥斯達黎加') ;
insert into teams values ('D4' ,'意大利') ;
insert into teams values ('E1' ,'瑞士') ;
insert into teams values ('E2' ,'厄瓜多') ;
insert into teams values ('E3' ,'洪都拉斯') ;
insert into teams values ('E4' ,'法國') ;
insert into teams values ('F1' ,'阿根廷') ;
insert into teams values ('F2' ,'奈及利亞') ;
insert into teams values ('F3' ,'伊朗') ;
insert into teams values ('F4' ,'波黑') ;
insert into teams values ('G1' ,'德國') ;
insert into teams values ('G2' ,'加納') ;
insert into teams values ('G3' ,'美國') ;
insert into teams values ('G4' ,'葡萄牙') ;
insert into teams values ('H1' ,'比利時') ;
insert into teams values ('H2' ,'阿爾及利亞') ;
insert into teams values ('H3' ,'南韓') ;
insert into teams values ('H4' ,'俄羅斯') ;
 
insert into match_results values ( (select  id from teams where name = '巴西') , 3 , (select  id from teams where name = '克羅地亞') , 1 ) ;
insert into match_results values ( (select  id from teams where name = '墨西哥') , 1 , (select  id from teams where name = '喀麥隆') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '巴西') , 0 , (select  id from teams where name = '墨西哥') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '西班牙') , 1 , (select  id from teams where name = '荷蘭') , 5 ) ;
insert into match_results values ( (select  id from teams where name = '智利') , 3 , (select  id from teams where name = '澳洲') , 1 ) ;
insert into match_results values ( (select  id from teams where name = '哥倫比亞') , 3 , (select  id from teams where name = '希臘') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '科特迪瓦') , 2 , (select  id from teams where name = '日本') , 1 ) ;
insert into match_results values ( (select  id from teams where name = '烏拉圭') , 1 , (select  id from teams where name = '哥斯達黎加') , 3 ) ;
insert into match_results values ( (select  id from teams where name = '英格蘭') , 1 , (select  id from teams where name = '意大利') , 2 ) ;
insert into match_results values ( (select  id from teams where name = '瑞士') , 2 , (select  id from teams where name = '厄瓜多') , 1 ) ;
insert into match_results values ( (select  id from teams where name = '法國') , 3 , (select  id from teams where name = '洪都拉斯') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '阿根廷') , 2 , (select  id from teams where name = '波黑') , 1 ) ;
insert into match_results values ( (select  id from teams where name = '伊朗') , 0 , (select  id from teams where name = '奈及利亞') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '德國') , 4 , (select  id from teams where name = '葡萄牙') , 0 ) ;
insert into match_results values ( (select  id from teams where name = '加納') , 1 , (select  id from teams where name = '美國') , 2 ) ;
insert into match_results values ( (select  id from teams where name = '比利時') , 2 , (select  id from teams where name = '阿爾及利亞' ) , 1 ) ;
insert into match_results values ( (select  id from teams where name = '俄羅斯') , 1 , (select  id from teams where name = '南韓' ) , 1 ) ;
           

檢視比賽結果：

select t1.name , m.team1_goals , t2.name , m.team2_goals
from match_results m join teams t1 on (m.TEAM1 = t1.ID ) join teams t2 on (m.TEAM2 = t2.ID ) ;
 
select substr( m.team1 , 0 , 1 ) as "小組" , t1.name , m.team1_goals , t2.name , m.team2_goals
from match_results m join teams t1 on (m.TEAM1 = t1.ID ) join teams t2 on (m.TEAM2 = t2.ID ) order by 1 ;
           

檢視積分榜：

with points as
( select team1 as team ,
case when team1_goals > team2_goals then 3 when team1_goals = team2_goals then 1 else 0 end as score ,
team1_goals goal , team2_goals goal_lost
from match_results
union all
select team2 as team ,
case when team2_goals > team1_goals then 3 when team2_goals = team1_goals then 1 else 0 end as score ,
team2_goals goal , team1_goals goal_lost
from match_results )
select substr( t.id , 0 , 1 ) as "分組", t.name as "國家" ,
sum(p.score) as "積分", sum(p.goal) as "進球數" , sum(p.goal_lost) as "失球數" ,  sum(p.goal) - sum(p.goal_lost) as "淨勝球"
from teams t left outer join points p on ( t.id = p.team )
group by ( substr( t.id , 0 , 1 ) , t.name)
order by 1 , 3 desc, 6 desc, 4 desc;  
 
with tmp as ( select team1 as team , case when team1_goals > team2_goals then 3 when team1_goals = team2_goals then 1 else 0 end as score
                from match_results
              union all
              select team2 as team , case when team2_goals > team1_goals then 3 when team2_goals = team1_goals then 1 else 0 end as score
                from match_results )
select substr( t.id , 0 , 1 ) , t.name , sum(s.score) from teams t left outer join tmp s on ( t.id = s.team ) group by ( substr( t.id , 0 , 1 ) , t.name)
order by 1 , 3 desc;  
           

以下語句會報錯，order by後面的列也必須在group by清單裡面。

with tmp as ( select team1 as team , case when team1_goals > team2_goals then 3 when team1_goals = team2_goals then 1 else 0 end as score
                from match_results
              union all
              select team2 as team , case when team2_goals > team1_goals then 3 when team2_goals = team1_goals then 1 else 0 end as score
                from match_results )
select substr( t.id , 0 , 1 ) , t.name , sum(s.score) from teams t left outer join tmp s on ( t.id = s.team ) group by ( substr( t.id , 0 , 1 ) , t.name)
order by 1 , t.ID ;