2021-05-28

最新推荐文章于 2022-01-04 11:05:23 发布

The Great Ant

最新推荐文章于 2022-01-04 11:05:23 发布

阅读量114

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_37698495/article/details/117375097

版权

sql 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

Team Scores in Football Tournament

需求一：写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

展示效果：

team_id	team_name	num_points
10	Leetcode FC	7
20	NewYork FC	3
50	Toronto FC	3
30	Atlanta FC	1
40	Chicago FC	0

Create table If Not Exists 62_Teams (team_id int, team_name varchar(30));
Create table If Not Exists 62_Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table 62_Teams;
insert into 62_Teams (team_id, team_name) values (10, 'Leetcode FC');
insert into 62_Teams (team_id, team_name) values (20, 'NewYork FC');
insert into 62_Teams (team_id, team_name) values (30, 'Atlanta FC');
insert into 62_Teams (team_id, team_name) values (40, 'Chicago FC');
insert into 62_Teams (team_id, team_name) values (50, 'Toronto FC');
Truncate table 62_Matches;
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (1, 10, 20, 30, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (2, 30, 10, 2, 2);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (3, 10, 50, 5, 1);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (4, 20, 30, 1, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (5, 50, 30, 1, 0);

最终SQL:

SELECT 
     *
FROM
    (SELECT 
           a.team_id,
           MAX(team_name) AS team_name,
           SUM(
                CASE 
			        WHEN a.team_id = b.host_team THEN 
				    CASE 
					    WHEN b.host_goals > b.guest_goals THEN 3
					    WHEN b.host_goals = b.guest_goals THEN 1
			            ELSE 0
				    END
			        WHEN a.team_id = b.guest_team THEN 
				    CASE 
					    WHEN b.host_goals < b.guest_goals THEN 3
					    WHEN b.host_goals = b.guest_goals THEN 1
					    ELSE 0
				    END
			        ELSE 0
		       END
           ) AS num_points
	FROM 
         62_Teams a
    LEFT JOIN
         62_Matches b
    ON 
         a.team_id = b.host_team OR 
         a.team_id = b.guest_team
	GROUP BY a.team_id
    ) a 
ORDER BY
    a.num_points DESC,
    a.team_id;