1841. 联赛信息统计 - 力扣（LeetCode）-CSDN博客

本文链接：https://blog.csdn.net/2301_78665414/article/details/144977085

1841. 联赛信息统计 - 力扣（LeetCode）
目标
1. 输入
  
  表: Teams
  team_id team_name
  1 Ajax
  4 Dortmund
  6 Arsenal
  
  表: Matches
  home_team_id away_team_id home_team_goals away_team_goals
  1 4 0 1
  1 6 3 3
  4 1 5 2
  6 1 0 0
2. 输出
  
  输出
  team_name matches_played points goal_for goal_against goal_diff
  Dortmund 2 6 6 2 4
  Arsenal 2 2 3 3 0
  Ajax 4 2 5 9 -4

分析

写一段SQL，用来报告联赛信息. 统计数据应使用已进行的比赛来构建，其中获胜球队获得三分，而失败球队获得零分。如果打平，两支球队都得一分。 result 表的每行应包含以下信息: team_name - Teams 表中的队伍名字 matches_played - 主场与客场球队进行的比赛次数. points - 球队获得的总分数. goal_for - 球队在所有比赛中获取的总进球数 goal_against - 球队在所有比赛中，他的对手球队的所有进球数 goal_diff - goal_for - goal_against. 按 points 降序返回结果表。如果两队或多队得分相同，则按 goal_diff 降序排列。如果仍然存在平局，则以 team_name 按字典顺序排列它们。













表: Teams		表: Matches					输出
team_id	team_name	home_team_id	away_team_id	home_team_goals	away_team_goals		team_name	matches_played	points	goal_for	goal_against	goal_diff
1	Ajax	1	4	0	1		Dortmund	2	6	6	2	4
4	Dortmund	1	6	3	3		Arsenal	2	2	3	3	0
6	Arsenal	4	1	5	2		Ajax	4	2	5	9	-4
		6	1	0	0
判断主场球队和客场球队的积分		home_team_id	away_team_id	home_team_goals	away_team_goals	home_team_score	away_team_score	队伍作为主队时的得分,对手得分,比赛积分UNION ALL 作为客队时的得分,对手得分,比赛积分,排序			team_id	goal_for	goal_against	team_score	分组聚合计算比赛次数,积分,进球,失球,净胜球	team_name	matches_played	points	goal_for	goal_against	goal_diff
		1	4	0	1	0	3				1	0	1	0		Dortmund	2	6	6	2	4
		1	6	3	3	1	1				1	3	3	1		Arsenal	2	2	3	3	0
		4	1	5	2	3	0				4	5	2	3		Ajax	4	2	5	9	-4
		6	1	0	0	1	1				6	0	0	1
											4	1	0	3
											6	3	3	1
											1	2	5	0
											1	0	0	1

实现

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