根据行中的标记获取变量 MIN Date [英] Get variable MIN Date based on marker in row

问题描述

我使用 MySQL 和 phpMyAdmin，下面是我的数据表:

I'm using MySQL with phpMyAdmin, below is my data table:

+--------+---------+-----------------+
| userID | context |      time       |
+--------+---------+-----------------+
|    111 |         | 7/1/2021        |
|    111 |         | 7/16/2019       |
|    111 | Reset   | 7/15/2019       |
|    222 |         | 7/9/2020        |
|    222 | Reset   | 7/8/2020        |
|    333 | Reset   | 5/11/2020       |
|    333 |         | 5/10/2020       |
|    444 |         | 9/8/2020        |
+--------+---------+-----------------+

我正在寻找一个 SELECT 查询，它给我 MIN 时间大于或等于 Reset 登录的日期context 列.如果 context 列中不存在 Reset 标记，我希望包含结果.

I'm looking for a SELECT query that gives me the MIN time greater or equal to the date where a Reset is logged in the context column. If no Reset marker exists in the context column, I'd like to have the result included.

所以对于上面的表格，我期待结果:

So for the table above, I expect the result:

+--------+-----------------+
| userID |      time       |
+--------+-----------------+
|    111 | 7/15/2019       |
|    222 | 7/8/2020        |
|    333 | 5/11/2020       |
|    444 | 9/8/2020        |
+--------+-----------------+

我试过了:

SELECT * FROM foo as a 
WHERE ((SELECT MIN(a.time) FROM foo) >= (SELECT Max(a.time) FROM foo where context = 'Reset')) group by userID

不会产生我预期的输出，但会返回:

does not produce my expected output, but returns:

+--------+-----------+
| userID |   time    |
+--------+-----------+
|    111 | 7/1/2021  |  <--- wrong 
|    222 | 7/8/2020  |
|    333 | 5/11/2020 |
+--------+-----------+

推荐答案

根据你的预期结果，你可以试试下面的查询-

Basis on your expected result, You may try below query -

SELECT userID, MAX(time)
  FROM YOUR_TABLE
 WHERE context = 'RESET'
 GROUP BY userID
 UNION ALL
SELECT userID, MAX(time)
  FROM YOUR_TABLE
 WHERE context IS NULL
 GROUP BY userID
 ORDER BY userID

这篇关于根据行中的标记获取变量 MIN Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！