基于10秒时间差匹配两张MariaDB表的最简SQL查询咨询

我有两张MariaDB表：

• 表check：包含user_id和time字段
• 表passing：包含user_id、time（与check表的time存在微小差异）和direction字段，部分场景下该表无对应check表的数据

需要查询得到user_id、check表的time（命名为time1）、passing表的time（命名为time2）和direction，要求time1与time2的时间差最大为10秒。我尝试过以下SQL语句，但结果不符合预期：

SELECT check.user_id, check.time, passing.time, passing.direction 
FROM check 
LEFT JOIN passing ON check.user_id = passing.user_id 
AND TIMESTAMPDIFF (SECOND, check.time, passing.time) < 10;

示例数据 #

check表数据 #

+---------+---------------------+
| user_id | time                |
+---------+---------------------+
|       3 | 2023-10-25 09:29:43 |
|      14 | 2023-10-25 09:28:39 |
|       5 | 2023-10-25 09:19:33 |
|       8 | 2023-10-25 09:18:46 |
|       9 | 2023-10-25 09:17:19 |
|      17 | 2023-10-25 09:16:46 |
|      18 | 2023-10-25 09:16:31 |
|      14 | 2023-10-25 09:15:46 |
|      16 | 2023-10-25 09:11:53 |
|      20 | 2023-10-25 09:07:53 |
|      20 | 2023-10-25 09:02:31 |
|       8 | 2023-10-25 09:02:25 |
|      17 | 2023-10-25 09:00:53 |
|       5 | 2023-10-25 09:00:49 |
|      18 | 2023-10-25 09:00:43 |
|      16 | 2023-10-25 09:00:28 |
|       9 | 2023-10-25 09:00:24 |
|      22 | 2023-10-25 09:00:16 |
|      22 | 2023-10-25 08:58:09 |
|      14 | 2023-10-25 08:49:46 |
+---------+---------------------+

passing表数据 #

+---------+---------------------+-----------+
| user_id | time                | direction |
+---------+---------------------+-----------+
|      14 | 2023-10-25 09:28:42 |         1 |
|       8 | 2023-10-25 09:18:49 |         1 |
|      17 | 2023-10-25 09:16:49 |         1 |
|      18 | 2023-10-25 09:16:34 |         1 |
|      14 | 2023-10-25 09:15:46 |         0 |
|      16 | 2023-10-25 09:11:55 |         1 |
|      20 | 2023-10-25 09:02:31 |         0 |
|       8 | 2023-10-25 09:02:25 |         0 |
|      17 | 2023-10-25 09:00:53 |         0 |
|       5 | 2023-10-25 09:00:49 |         0 |
|      18 | 2023-10-25 09:00:43 |         0 |
|      16 | 2023-10-25 09:00:28 |         0 |
|       9 | 2023-10-25 09:00:24 |         0 |
|      22 | 2023-10-25 09:00:19 |         1 |
|      22 | 2023-10-25 08:58:09 |         0 |
|      14 | 2023-10-25 08:49:49 |         1 |
|      14 | 2023-10-25 08:47:53 |         0 |
|       2 | 2023-10-25 08:46:53 |         1 |
|       2 | 2023-10-25 08:44:54 |         0 |
|       4 | 2023-10-25 08:32:14 |         0 |
+---------+---------------------+-----------+

期望查询结果 #

+---------+---------------------+---------------------+-----------+
| user_id | time                | time                | direction |
+---------+---------------------+---------------------+-----------+
|       3 | 2023-10-25 09:29:43 |                     |           |
|      14 | 2023-10-25 09:28:39 | 2023-10-25 09:28:42 |         1 |
|       5 | 2023-10-25 09:19:33 |                     |           |
|       8 | 2023-10-25 09:18:46 | 2023-10-25 09:18:49 |         1 |
|       9 | 2023-10-25 09:17:19 |                     |           |
|      17 | 2023-10-25 09:16:46 | 2023-10-25 09:16:49 |         1 |
|      18 | 2023-10-25 09:16:31 | 2023-10-25 09:16:34 |         1 |
|      14 | 2023-10-25 09:15:46 | 2023-10-25 09:15:46 |         0 |
|      16 | 2023-10-25 09:11:53 | 2023-10-25 09:11:55 |         1 |
|      20 | 2023-10-25 09:07:53 |                     |           |
|      20 | 2023-10-25 09:02:31 | 2023-10-25 09:02:31 |         0 |
|       8 | 2023-10-25 09:02:25 | 2023-10-25 09:02:25 |         0 |
|      17 | 2023-10-25 09:00:53 | 2023-10-25 09:00:53 |         0 |
|       5 | 2023-10-25 09:00:49 | 2023-10-25 09:00:49 |         0 |
|      18 | 2023-10-25 09:00:43 | 2023-10-25 09:00:43 |         0 |
|      16 | 2023-10-25 09:00:28 | 2023-10-25 09:00:28 |         0 |
|       9 | 2023-10-25 09:00:24 | 2023-10-25 09:00:24 |         0 |
|      22 | 2023-10-25 09:00:16 | 2023-10-25 09:00:19 |         1 |
|      22 | 2023-10-25 08:58:09 | 2023-10-25 08:58:09 |         0 |
|      14 | 2023-10-25 08:49:46 | 2023-10-25 08:49:49 |         1 |
+---------+---------------------+---------------------+-----------+

原SQL存在两个问题：

1. 仅判断了check.time晚于passing.time的时间差，未覆盖passing.time更早的情况，需取时间差的绝对值
2. 可能出现一条check记录匹配多条passing记录的情况，需确保每条check仅匹配最接近的passing记录

修正后SQL（窗口函数版，高效且逻辑清晰） #

SELECT 
    user_id, 
    time1, 
    time2, 
    direction
FROM (
    SELECT 
        c.user_id, 
        c.time AS time1, 
        p.time AS time2, 
        p.direction,
        -- 按每条check记录分组，匹配的passing记录按时间差绝对值排序
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.time 
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, c.time, p.time)) ASC
        ) AS rn
    FROM `check` c
    LEFT JOIN passing p 
        ON c.user_id = p.user_id 
        -- 时间差绝对值不超过10秒
        AND ABS(TIMESTAMPDIFF(SECOND, c.time, p.time)) <= 10
) t
-- 仅保留每组中最接近的匹配记录
WHERE rn = 1;

逻辑说明 #

• 使用ABS(TIMESTAMPDIFF(...)) <=10确保时间差绝对值不超过10秒，覆盖两种时间先后场景
• 窗口函数ROW_NUMBER()对每条check记录的匹配结果排序，取时间差最小的一条，避免重复匹配
• 保留LEFT JOIN逻辑，无匹配的passing记录会显示为NULL，符合需求

内容的提问来源于stack exchange，提问作者MNorb