mysql - MySQL:从临时表中聚合多个对象的更改
问题描述
我有一张这样的表格,其中保存了每个角色的每个状态更改。
table characters_changes
+----+--------+---------+-------+----------------------+
| id | rank | job | money | datetime |
+----+--------+---------+-------+----------------------+
| 1 | 2 | tailor | 25 | 2018-06-01 12:30:15 |
| 1 | 3 | NULL | 5 | 2018-06-02 10:50:19 |
| 1 | 2 | NULL | -5 | 2018-06-03 18:44:35 |
| 1 | NULL | tinker | 10 | 2018-06-04 04:10:12 |
| 1 | 3 | NULL | NULL | 2018-06-05 17:31:00 |
| 2 | 1 | spy | 7 | 2018-06-01 12:30:15 |
| 2 | 2 | NULL | NULL | 2018-06-02 10:50:19 |
| 2 | NULL | no job | 7 | 2018-06-03 17:31:00 |
| 3 | 3 | soldier | 12 | 2018-06-01 12:30:15 |
| 3 | 1 | NULL | -11 | 2018-06-02 10:50:19 |
+----+--------+---------+-------+----------------------+
NULL
表示对应属性没有变化。职级和工作变动意味着用另一个替换,而货币变动意味着加减总和(如果不是NULL
)。保证每个字符至少有一行,没有任何NULL
-s。
所以我需要一个表格,最后我可以显示每个字符的当前状态。与他们的最后一个职位,最后一份工作和由此产生的金钱。像这样的一张桌子。
table characters_status
+----+--------+---------+-------+
| id | rank | job | money |
+----+--------+---------+-------+
| 1 | 3 | tinker | 35 |
| 2 | 2 | no job | 14 |
| 3 | 1 | soldier | 1 |
+----+--------+---------+-------+
更糟糕的是,tablecharacters_changes
是一个临时表。其中的日期时间来自另一个事件表。所以因为它是临时的,我只能查询一次。但是可以有任意数量的字符,并且很可能会有更多的列,如等级和工作。
需要整个系统通过忽略之后的所有更改来提供在任何给定日期时间获取所有字符状态的可能性。但这部分对我来说很容易,所以我把它排除在我的问题范围之外。
解决方案
这应该有效,我认为:
SELECT id
, CASE WHEN INSTR(ranks, '|') = 0 THEN ranks ELSE LEFT(ranks, INSTR(ranks, '|')-1) END AS rank
, CASE WHEN INSTR(jobs, '|') = 0 THEN jobs ELSE LEFT(jobs, INSTR(jobs, '|')-1) END AS job
, monies
FROM
(
SELECT id
, GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|') AS ranks
, GROUP_CONCAT(job ORDER BY datetime DESC SEPARATOR '|') AS jobs
, SUM(money) AS monies
FROM characters_changes
GROUP BY id
) AS lists
;
从技术上讲,您可以在没有子查询的情况下执行此操作,但为了清楚起见,我将其分解为这种方式。另一种方法是这样的表达式:
, CASE COUNT(rank) WHEN 0 THEN NULL WHEN 1 THEN GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|') ELSE LEFT(GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|'), INSTR(GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|'), '|')-1) END AS rank
推荐阅读
- c# - LINQ 为异步方法选择模拟
- python - 尽管使用 POST,但不允许使用 Flask- 方法
- c# - 将文本和应用程序特定数据复制到剪贴板
- c++ - unzip.obj 可能缺少哪些库:错误 LNK2019?
- javascript - Css 导致 Angularjs 动态 div 结果到 Jam
- javascript - Vue:如何知道用户选择/突出显示的文本并显示多个实例
- r - 清除全局环境,保留 new.env() 创建的环境
- php - move_uploaded_file( ) 不适用于视频/音频/pdf
- java - 在 Java 中从超类类型的 ArrayList 调用子类方法
- javascript - 未捕获的类型错误:无法将属性“禁用”设置为 null