实现 DROP TABLE IF EXISTS matches;

Create table If Not Exists Teams (team_id int, team_name varchar(20));

Create table If Not Exists Matches (home_team_id int, away_team_id int, home_team_goals int, away_team_goals int);

Truncate table Teams;

insert into Teams (team_id, team_name) values ('1', 'Ajax');

insert into Teams (team_id, team_name) values ('4', 'Dortmund');

insert into Teams (team_id, team_name) values ('6', 'Arsenal');

Truncate table Matches;

insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('1', '4', '0', '1');

insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('1', '6', '3', '3');

insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('4', '1', '5', '2');

insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('6', '1', '0', '0');

SELECT * FROM teams;

SELECT * FROM matches;

# 判断主场球队和客场球队的积分

SELECT *,

CASE WHEN home_team_goals > away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END home_team_score,

CASE WHEN home_team_goals < away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END away_team_score

FROM matches;

# 队伍作为主队时的得分,对手得分,比赛积分UNION ALL 作为客队时的得分,对手得分,比赛积分,排序

WITH t1 AS ( SELECT *,

CASE WHEN home_team_goals > away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END home_team_score,

CASE WHEN home_team_goals < away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END away_team_score

FROM matches )

SELECT home_team_id team_id ,home_team_goals goal_for,away_team_goals goal_against ,home_team_score team_score

FROM t1

UNION ALL

SELECT away_team_id,away_team_goals,home_team_goals,away_team_score

FROM t1;

# 分组聚合计算比赛次数,积分,进球,失球,净胜球

WITH t1 AS ( SELECT *,

CASE WHEN home_team_goals > away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END home_team_score,

CASE WHEN home_team_goals < away_team_goals THEN 3

WHEN home_team_goals = away_team_goals THEN 1

ELSE 0 END away_team_score

FROM matches )

,t2 AS ( SELECT home_team_id team_id ,home_team_goals goal_for,away_team_goals goal_against ,home_team_score team_score

FROM t1

UNION ALL

SELECT away_team_id,away_team_goals,home_team_goals,away_team_score

FROM t1 )

SELECT team_name,count(t2.team_id)matches_played ,sum(team_score) points,sum(goal_for)goal_for,sum(goal_against)goal_against,sum(goal_for)-sum(goal_against) goal_diff

FROM t2

JOIN teams ON Teams.team_id=t2.team_id

GROUP BY team_name

ORDER BY points DESC,goal_diff DESC,team_name