LeetCode_sql_day18(1841.联赛信息统计)

2024-08-31 01:52

本文主要是介绍LeetCode_sql_day18(1841.联赛信息统计),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

描述

表: Teams

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| team_id        | int     |
| team_name      | varchar |
+----------------+---------+
team_id 是该表主键.
每一行都包含了一个参加联赛的队伍信息.

表: Matches

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| home_team_id    | int     |
| away_team_id    | int     |
| home_team_goals | int     |
| away_team_goals | int     |
+-----------------+---------+
(home_team_id, away_team_id) 是该表主键.
每一行包含了一次比赛信息.
home_team_goals 代表主场队得球数.
away_team_goals 代表客场队得球数.
获得球数较多的队伍为胜者队伍.

写一段SQL,用来报告联赛信息. 统计数据应使用已进行的比赛来构建,其中 获胜 球队获得 三分 ,而失败球队获得 零分 。如果 打平 ,两支球队都得 一分 

result 表的每行应包含以下信息:

  • team_name - Teams 表中的队伍名字
  • matches_played - 主场与客场球队进行的比赛次数.
  • points - 球队获得的总分数.
  • goal_for - 球队在所有比赛中获取的总进球数
  • goal_against - 球队在所有比赛中,他的对手球队的所有进球数
  • goal_diff - goal_for - goal_against.

按 points 降序 返回结果表。 如果两队或多队得分相同,则按 goal_diff 降序 排列。 如果仍然存在平局,则以 team_name 按字典顺序 排列它们。

查询的结果格式如下例所示。

示例 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               |
+--------------+--------------+-----------------+-----------------+
输出:
+-----------+----------------+--------+----------+--------------+-----------+
| 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        |
+-----------+----------------+--------+----------+--------------+-----------+
解释:
Ajax (team_id=1) 有4场比赛: 2败2平. 总分数 = 0 + 0 + 1 + 1 = 2.
Dortmund (team_id=4) 有2场比赛: 2胜. 总分数 = 3 + 3 = 6.
Arsenal (team_id=6) 有2场比赛: 2平. 总分数 = 1 + 1 = 2.
Dortmund 是积分榜上的第一支球队. Ajax和Arsenal 有同样的分数, 但Arsenal的goal_diff高于Ajax, 所以Arsenal在表中的顺序在Ajaxzhi'qian.

数据准备

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 *,casewhen home_team_goals > away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals < away_team_goals then 0end as home_team_points,casewhen home_team_goals < away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals > away_team_goals then 0end as away_team_pointsfrom Matches

②然后分别计算球队比赛次数(主队的次数+客队的次数)、球队总得分(主队时的得分+客队时的得分)、球队总进球数(主队时的总进球数+客队时的总进球数)、对手总进球数(作为主队时对手作为客队的进球数+作为客队时对手作为主队的总进球数)

with t1 as (select *,casewhen home_team_goals > away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals < away_team_goals then 0end as home_team_points,casewhen home_team_goals < away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals > away_team_goals then 0end as away_team_pointsfrom Matches)
select distinct team_name,(select count(1) from t1 where home_team_id = Matches.home_team_id or away_team_id =Matches.home_team_id)  as matches_played,(select sum(home_team_points) from t1 where home_team_id = Matches.home_team_id) +(select sum(away_team_points) from t1 where away_team_id = Matches.home_team_id) as points,(select sum(home_team_goals) from t1 where home_team_id = Matches.home_team_id) +(select sum(away_team_goals) from t1 where away_team_id = Matches.home_team_id) as goal_for,(select sum(away_team_goals) from t1 where home_team_id = Matches.home_team_id) +(select sum(home_team_goals) from t1 where away_team_id = Matches.home_team_id) as goal_against
from matches , teams where matches.home_team_id = teams.team_idunion
select distinct team_name,(select count(1) from t1 where away_team_id = Matches.away_team_id or home_team_id =Matches.away_team_id)  as matches_played,(select ifnull(sum(home_team_points),0 ) from t1 where home_team_id = Matches.away_team_id) +(select ifnull(sum(away_team_points),0) from t1 where away_team_id = Matches.away_team_id) as points,(select ifnull(sum(home_team_goals),0) from t1 where home_team_id = Matches.away_team_id) +(select ifnull(sum(away_team_goals),0) from t1 where away_team_id = Matches.away_team_id) as goal_for,(select ifnull(sum(away_team_goals),0) from t1 where home_team_id = Matches.away_team_id) +(select ifnull(sum(home_team_goals),0) from t1 where away_team_id = Matches.away_team_id) as goal_against
from matches , teams where matches.away_team_id = teams.team_id

③基于上述结果 求goal_diff并且按照题目要求排序

select          team_name,matches_played,points,goal_for,goal_against,(goal_for-goal_against) as goal_diff from t2
order by points desc,goal_diff desc,team_name desc;

图解:

输入
home_team_idaway_team_idhome_team_goalsaway_team_goalshome_team_pointsaway_team_pointsteam_idteam_name
1401034Dortmund
1633111Ajax
4152306Arsenal
610011
分别求出各队作为
主队和客队时的分数、球数
结果
team_namematches_playedpointsgoal_forgoal_against
结果(最终)Dortmund2662
主队的+客队的主队的+客队的主队的+客队的主队的+客队的主队的+客队的Arsenal2233
Ajax4259
在此基础上求出goal_diff
team_namematches_playedpointsgoal_forgoal_againstgoal_diff
Dortmund26624
Arsenal22330
Ajax4259-4

代码

with t1 as (select *,casewhen home_team_goals > away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals < away_team_goals then 0end as home_team_points,casewhen home_team_goals < away_team_goals then 3when home_team_goals = away_team_goals then 1when home_team_goals > away_team_goals then 0end as away_team_pointsfrom Matches)
, t2 as (
select home_team_id,(select count(1) from t1 where home_team_id = Matches.home_team_id or away_team_id =Matches.home_team_id)             as matches_played,(select sum(home_team_points) from t1 where home_team_id = Matches.home_team_id) +(select sum(away_team_points) from t1 where away_team_id = Matches.home_team_id) as points,(select sum(home_team_goals) from t1 where home_team_id = Matches.home_team_id) +(select sum(away_team_goals) from t1 where away_team_id = Matches.home_team_id) as goal_for,(select sum(away_team_goals) from t1 where home_team_id = Matches.home_team_id) +(select sum(home_team_goals) from t1 where away_team_id = Matches.home_team_id) as goal_against
#         goal_for-goal_against as goal_diff
from matchesunion all
(select away_team_id,(select count(1) from t1 where away_team_id = Matches.away_team_id or home_team_id =Matches.away_team_id)             as matches_played,(select sum(away_team_points) from t1 where away_team_id = Matches.away_team_id) +(select sum(home_team_points) from t1 where home_team_id = Matches.away_team_id) as points,(select sum(home_team_goals) from t1 where home_team_id = Matches.away_team_id) +(select sum(away_team_goals) from t1 where away_team_id = Matches.away_team_id) as goal_for,(select sum(away_team_goals) from t1 where home_team_id = Matches.away_team_id) +(select sum(home_team_goals) from t1 where away_team_id = Matches.away_team_id) as goal_againstfrom Matches)
)select distinct (select team_name from teams where team_id=t2.home_team_id)team_name,matches_played,points,goal_for,goal_against,(goal_for-goal_against) as goal_diff from t2
order by points desc,goal_diff desc,team_name;

总结

最后要考虑到有的球队只有客队场 所以使用union 既要关联到主队id又要关联到客队id

这篇关于LeetCode_sql_day18(1841.联赛信息统计)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1122552

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

一文详解如何使用Java获取PDF页面信息

《一文详解如何使用Java获取PDF页面信息》了解PDF页面属性是我们在处理文档、内容提取、打印设置或页面重组等任务时不可或缺的一环,下面我们就来看看如何使用Java语言获取这些信息吧... 目录引言一、安装和引入PDF处理库引入依赖二、获取 PDF 页数三、获取页面尺寸(宽高)四、获取页面旋转角度五、判断