mysql - 根据其他行值调整 MIN(date)
问题描述
桌子:
+----+--------+------------+------+---------+-----------------+
| id | userID | difference | type | context | time |
+----+--------+------------+------+---------+-----------------+
| 83 | 111 | 30 | | | 7/15/2019 15:23 |
| 84 | 111 | 10 | | | 7/16/2019 15:28 |
| 85 | 111 | -10 | | Reset | 7/16/2020 15:28 |
| 86 | 222 | 50 | | | 7/8/2020 15:28 |
| 87 | 222 | -10 | | Reset | 7/8/2020 15:28 |
| 88 | 333 | -10 | | Reset | 5/11/2020 13:15 |
| 89 | 333 | 10 | | | 7/16/2019 13:16 |
| 91 | 111 | 20 | | | 7/17/2019 23:15 |
+----+--------+------------+------+---------+-----------------+
我正在寻找一个查询,该查询返回每个用户 ID 的“差异”(> 0)的总和,该用户 ID 的最小日期为 '16/7/2019' 并带有例外,如果此用户 ID 在上下文中具有重置必须忽略小于记录重置的最小日期的行。
所以输出应该是这样的:
+--------+-------------+
| userID | SUM(points) |
+--------+-------------+
| 111 | 30|
+--------+-------------+
我的尝试:
SELECT userID, SUM(case when difference > 0 then difference else 0 end),
FROM test WHERE (CASE
WHEN (Select MAX(CAST(time as DATE)) where context ='Reset') > (Select
MIN(CAST(time as DATE))) THEN (Select MAX(CAST(time as DATE)) where
context ='Reset') ELSE (Select MIN(CAST(time as DATE))) END) = '7/16/2019' group by userID
继承人为表创建:
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`userID` int(11) NOT NULL,
`difference` int(11) NOT NULL,
`type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`context` text COLLATE utf8_unicode_ci NOT NULL,
`time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `test` (`id`, `userID`, `difference`, `type`, `context`, `time`) VALUES
(83, 111, 30, '', '', '2019-07-15 15:23:22'),
(84, 111, 10, '', '', '2019-07-16 15:28:27'),
(85, 111, -10, '', 'Reset', '2020-07-16 15:28:27'),
(86, 222, 50, '', '', '2020-07-08 15:28:27'),
(87, 222, -10, '', 'Reset', '2020-07-08 15:28:27'),
(88, 333, -10, '', 'Reset', '2020-05-11 13:15:05'),
(89, 333, -10, '', '', '2019-07-16 13:16:35'),
(91, 111, 20, '', '', '2019-07-17 23:15:57');
解决方案
尝试这样的事情。对于每个用户,找到重置日期并按旧记录过滤
SELECT
t1.userId, SUM(points)
FROM
table1 t1
LEFT JOIN (
SELECT t2.userId, max(t2.date) date
FROM table2 t2
WHERE context = "Reset"
GROUP BY t2.userId
) resetDates on resetDates.userId = t1.userId
WHERE
t1.date >= IFNULL(resetDates.date, "01-01-2020")
GROUP BY
t1.userId;
推荐阅读
- r - 如何使用 caret 包解释模型输出的准确性
- php - 向 JSON 对象添加多个图像 url
- arrays - 获取数组的总和,保存到核心数据,并在重新打开应用程序后从总和开始?
- c# - 如果通过docusign api取消事件类型,则将嵌入式签名者转换为远程签名者
- ruby-on-rails - 使用 form_with 时视图不呈现,具有 url 和 ROR 中的现有控制器操作
- c# - 通过网络流发送字节数组时,最终文件中会出现额外字符
- c++ - libc++abi 未定义符号:_ZTVN10__cxxabiv120__si_class_type_infoE
- centos7 - rsyslog 变量不起作用
- javascript - 如何隐藏轮播中单个图像的下一个和上一个按钮?
- faker - faker npm 包,用于获取具有特定性别的名称