mysql - 将表 a 中的最新行与表 b 中的每行连接起来
问题描述
注意标题,我不知道如何准确地表达这个词,但我认为它应该解释我想要实现的目标。
我有两张桌子,一张用于“玩家”,每个玩家只有一行,第二张桌子由有关所有玩家的“事件”组成,其中包括对玩家帐户的各种更改。
DESCRIBE PLAYERS;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| ORIGINAL_REGION | varchar(4) | NO | | NULL | |
| REGION | tinyint(4) | NO | | NULL | |
| ACCOUNT_ID | bigint(20) | NO | | NULL | |
| USERNAME | varchar(16) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
DESCRIBE EVENTS;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| ORIGINAL_REGION | varchar(4) | NO | | NULL | |
| ACCOUNT_ID | bigint(20) | NO | | NULL | |
| TIMESTAMP | bigint(20) | NO | | NULL | |
| PREVIOUS_NAME | varchar(16) | NO | | NULL | |
| NAME | varchar(16) | NO | | NULL | |
| RENAMED | tinyint(1) | NO | | NULL | |
| PREVIOUS_REGION | tinyint(4) | NO | | NULL | |
| REGION | tinyint(4) | NO | | NULL | |
| TRANSFER | tinyint(1) | NO | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
要获取所有玩家和任何相关事件的列表,我可以在玩家上留下加入事件
SELECT * FROM PLAYERS P LEFT JOIN EVENTS E ON P.ACCOUNT_ID=E.ACCOUNT_ID AND P.ORIGINAL_REGION=E.ORIGINAL_REGION;
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 1 | EUW1 | 2592238782309408 | 1626896911435 | hawot2541 | XRO SCRIPTKID 2 | 1 | 3 | 3 | 0 |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 3 | EUW1 | 2592238782309408 | 1626897970705 | XRO SCRIPTKID 2 | XRO SCRIPTKID 2 | 0 | 3 | 2 | 1 |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 4 | EUW1 | 2592238782309408 | 1626898970705 | XRO SCRIPTKID 2 | sir hawloT | 1 | 2 | 4 | 1 |
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
我现在的问题是我无法查询此表以仅包含每个玩家的最新事件(基于时间戳)。
所需的输出如下所示:
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 4 | EUW1 | 2592238782309408 | 1626898970705 | XRO SCRIPTKID 2 | sir hawloT | 1 | 2 | 4 | 1 |
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
我尝试了多个子查询,无论我能在网上找到什么查询。可悲的是,没有人给出我需要的结果。我得到的最接近的是使用 GROUP BY ACCOUNT_ID,ORIGINAL_REGION 但这不允许我选择具有最新时间戳值的那个。
SELECT * FROM PLAYERS P LEFT JOIN EVENTS E ON P.ACCOUNT_ID=E.ACCOUNT_ID AND P.ORIGINAL_REGION=E.ORIGINAL_REGION GROUP BY P.ACCOUNT_ID,P.ORIGINAL_REGION;
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 1 | EUW1 | 2592238782309408 | 1626896911435 | hawot2541 | XRO SCRIPTKID 2 | 1 | 3 | 3 | 0 |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
我现在已经尝试了几个小时,希望这里有人能告诉我这是否可能。
解决方案
推荐阅读
- pine-script - 有人可以告诉我如何解决我的 Tradingview 代码要求吗?
- python - 谁能帮我用 Python 处理数据?
- apache-kafka - 我想克服使用 Kafka Spooldir 连接器时遇到的一些限制
- c# - C# 在运行时更改部分类的默认执行顺序
- reactjs - 来自对象的图像 require(url) 不显示在 React 中
- vue.js - 选择标签中的属性:值绑定不会在 Vue 3 组件模板(组合 API)中更新
- r - 更改各种变量中的特定元素
- swift - json解码限制
- java - 骆驼读取文件和异常将文件移动到错误文件夹
- docker - docker-compose 类型:卷保存在外部文件夹中