MySQL - 为每组日期选择最大日期

Question

我有下表:

SELECT * FROM checkin;

+----+--------+---------------------+
| id | userid |    date_checked     |
+----+--------+---------------------+
| 1  |   15   | 2019-10-08 10:22:15 | 
| 2  |   15   | 2019-10-08 10:22:42 | 
| 3  |   15   | 2019-10-08 10:23:01 | 
| 4  |   40   | 2019-10-08 10:23:44 | 
| 5  |   15   | 2019-10-08 10:24:06 | 
| 6  |   15   | 2019-10-08 10:24:09 | 
| 7  |    3   | 2019-10-08 10:24:59 | 
+----+--------+---------------------+

我想为每组连续相等的用户 ID 选择具有最高 date_checked 的行。在这个例子中，我希望是这样的:

+----+--------+---------------------+
| id | userid |    date_checked     |
+----+--------+---------------------+
| 3  |   15   | 2019-10-08 10:23:01 | 
| 4  |   40   | 2019-10-08 10:23:44 | 
| 6  |   15   | 2019-10-08 10:24:09 | 
| 7  |    3   | 2019-10-08 10:24:59 | 
+----+--------+---------------------+

请在标记为“duplicated of”之前注意:我不喜欢 userid 列的 DISTINTC，相同的 userid 可以重复多次。

我该怎么做？

Answer 1

这是一个典型的缺口和孤岛问题。假设你使用的是MySQL 8.0，你可以使用ROW_NUMBER()来解决:

SELECT
    MAX(id) id,
    userid,
    MAX(date_checked) date_checked
FROM (
     SELECT t.*,
        ROW_NUMBER() OVER(ORDER BY date_checked) rn1,
        ROW_NUMBER() OVER(PARTITION BY userid ORDER BY date_checked) rn2
    FROM checkin t
) x
GROUP BY rn1 - rn2, userid