mysql - 如果同一用户在一小时内重复访问，则使用 MySQL 时，只有第一个被认为是有效的

Question

我需要使用数据库 MySql 版本 5.7更新valid_access表上列的现有值dotable

+----+------------+----------+-----------+--------------+
| id | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
+----+------------+----------+-----------+--------------+
|  1 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  2 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  3 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  4 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  5 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  6 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  7 | 2021-08-02 | 02:44:00 | D29942    | NULL         |
+----+------------+----------+-----------+--------------+

我需要这个输出

+----+------------+----------+-----------+--------------+
| id | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
+----+------------+----------+-----------+--------------+
|  1 | 2021-08-02 | 00:39:31 | D29942    | 1            |
|  2 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  3 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  4 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  5 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  6 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  7 | 2021-08-02 | 02:44:00 | D29942    | 1            |
+----+------------+----------+-----------+--------------+

因为如果同一用户在一小时内重复访问，则只有第一个被认为是有效的。

有什么建议吗？

我的表结构和下面db-fiddle.com上的更新查询，它提供 MySQL 5.7

标签： mysql

Answer 1

假设您要标记相隔 1 小时的登录，查询将是：

SELECT *
FROM dotable AS curr
WHERE NOT EXISTS (
    SELECT *
    FROM dotable AS prev
    WHERE prev.user_code = curr.user_code
    AND TIMESTAMP(prev.yyyy_mm_dd, prev.hh_mm_ss) > TIMESTAMP(curr.yyyy_mm_dd, curr.hh_mm_ss) - INTERVAL 1 HOUR
    AND (
        TIMESTAMP(prev.yyyy_mm_dd, prev.hh_mm_ss) < TIMESTAMP(curr.yyyy_mm_dd, curr.hh_mm_ss)
        OR
        TIMESTAMP(prev.yyyy_mm_dd, prev.hh_mm_ss) = TIMESTAMP(curr.yyyy_mm_dd, curr.hh_mm_ss) AND prev.id < curr.id
    )
)