LeetCode_sql_day18（1841.联赛信息统计）

2024-08-31 01:52
文章标签 leetcode sql day18 1841 联赛 统计 信息 database

本文主要是介绍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。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
哈希leetcode-1

哈希leetcode-1

目录 1前言 2.例题  2.1两数之和 2.2判断是否互为字符重排 2.3存在重复元素1 2.4存在重复元素2 2.5字母异位词分组 1前言 哈希表主要是适合于快速查找某个元素（O(1)） 当我们要频繁的查找某个元素，第一哈希表O(1)，第二，二分O(log n) 一般可以分为语言自带的容器哈希和用数组模拟的简易哈希。 最简单的比如数组模拟字符存储，只要开26个c
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
hdu1496（用hash思想统计数目）

hdu1496（用hash思想统计数目）

作为一个刚学hash的孩子，感觉这道题目很不错，灵活的运用的数组的下标。 解题步骤：如果用常规方法解，那么时间复杂度为O（n^4）,肯定会超时，然后参考了网上的解题方法，将等式分成两个部分，a*x1^2+b*x2^2和c*x3^2+d*x4^2， 各自作为数组的下标，如果两部分相加为0，则满足等式； 代码如下： #include<iostream>#include<algorithm
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
业务中14个需要进行A/B测试的时刻[信息图]

业务中14个需要进行A/B测试的时刻[信息图]

在本指南中，我们将全面了解有关 A/B测试 的所有内容。 我们将介绍不同类型的A/B测试，如何有效地规划和启动测试，如何评估测试是否成功，您应该关注哪些指标，多年来我们发现的常见错误等等。 什么是A/B测试？ A/B测试（有时称为“分割测试”）是一种实验类型，其中您创建两种或多种内容变体——如登录页面、电子邮件或广告——并将它们显示给不同的受众群体，以查看哪一种效果最好。 本质上，A/B测
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2