mysql - MySQL - 从前一行中减去值,按 2 个字段分组
解决方案
可以这样做:
SELECT
t.myMax,
t.`insertat`,
IF(t.myMin = t.myMax, t1.myval, t2.`myval` - t1.`myval`) AS newval
FROM (
SELECT insertat, min(id) AS myMin, max(id) AS myMax
FROM myTable
GROUP BY insertat) AS t
LEFT JOIN myTable t1 ON t1.id = t.myMin
LEFT JOIN myTable t2 ON t2.id = t.myMax;
样本
MariaDB [bernd]> SELECT * FROM myTable;
+----+------------+-------+
| id | insertat | myval |
+----+------------+-------+
| 1 | 2021-01-01 | 44 |
| 2 | 2021-01-02 | 99 |
| 3 | 2021-01-02 | 134 |
| 4 | 2021-01-03 | 45 |
| 5 | 2021-01-04 | 2 |
| 6 | 2021-01-04 | 17 |
+----+------------+-------+
6 rows in set (0.01 sec)
MariaDB [bernd]>
MariaDB [bernd]> SELECT
-> t.myMax,
-> t.`insertat`,
-> IF(t.myMin = t.myMax, t1.myval, t2.`myval` - t1.`myval`) AS newval
-> FROM (
-> SELECT insertat, min(id) AS myMin, max(id) AS myMax
-> FROM myTable
-> GROUP BY insertat) AS t
-> LEFT JOIN myTable t1 ON t1.id = t.myMin
-> LEFT JOIN myTable t2 ON t2.id = t.myMax;
+-------+------------+--------+
| myMax | insertat | newval |
+-------+------------+--------+
| 1 | 2021-01-01 | 44 |
| 3 | 2021-01-02 | 35 |
| 4 | 2021-01-03 | 45 |
| 6 | 2021-01-04 | 15 |
+-------+------------+--------+
4 rows in set (0.03 sec)
MariaDB [bernd]>
推荐阅读
- linux - 从星期一开始的月份
- javascript - Javascript 事件侦听器单击多个
- javascript - Express.js - 点击 url 从 api 下载文件
- apache - 服务器重定向
- apache - Mod Rewrite 停止规则?
- git - Bit local repo:从旧提交中删除文件
- npm - 在 PhpStorm 中配置 node-sass
- javascript - 什么是 TypeError: require(...)(...) is not a function 当我尝试运行我的 playWright 脚本时?
- xml - 如何检索 XML 参数实体的值?
- kubernetes - 用于 2 组注释的 Helm 模板