我正在使用带有PhpMyAdmin表的MySQL:
+--------+---------+-----------------+
| 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 |
+--------+---------+-----------------+
我正在寻找一个选择，它给我的最小时间大于或等于在上下文列中记录重置的日期。 如果没有重置标记在表中，我想有reslt包括在内。
因此，对于上表，我希望得到以下结果:
+--------+-----------------+
| 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
不执行我所期望的操作，而是返回:
+--------+-----------+
| userID | time |
+--------+-----------+
| 111 | 7/1/2021 | <--- wrong
| 222 | 7/8/2020 |
| 333 | 5/11/2020 |
+--------+-----------+
使用窗口函数:
select t.*,
coalesce(next_time, time) as imputed_time
from (select t.*,
sum(context = 'reset') over (partition by user_id) as cnt_reset,
min(case when context is null then time end) over (partition by userid order by time rows between current row and unbounded following) as next_time
from t
) t
where cnt_reset = 0 or context = 'reset';
根据您的预期结果，您可以尝试下面的查询-
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
您可以使用windows函数，如下所示:
select userid, min(case when sm = 0 then mindt else resetmindt end) as mindate
from
(select t.*, sum(case when context = 'reset' then 1 end) over (partition by userid) as sm,
min(date) over (partition by userid) as Mindt,
min(case when context = 'reset' then date end) over (partition by userid) resetmindt
from your_Table t)
where group by userid
