用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 ;