如何在MySQL中使用JOIN匹配符合特定时间条件的记录

原始数据集 #

originating_date_time   number  operation
12/27/2022 16:26:39      11123    type1
12/27/2022 16:27:07      11232    type2
12/27/2022 16:27:11      11232    type3
12/27/2022 16:27:01      11245    type2
12/27/2022 16:27:13      11245    type3
12/27/2022 16:27:19      11239    type2
12/27/2022 16:27:39      11249    type3

筛选规则 #

• 忽略所有operation为type1的记录
• 仅保留同一number下，operation分别为type2和type3且两条记录时间差小于7秒的配对
• 排除时间差大于7秒的type2/type3配对、仅存在type2或仅存在type3的记录

原SQL的问题 #

你的SQL存在几个逻辑缺陷：

1. 未明确限定t1和t2的operation类型，可能出现同类型配对（如type2和type2），甚至匹配到type1记录
2. 用TIMEDIFF(...) = '00:00:07'严格等于7秒，不符合“小于7秒”的要求
3. 未限制时间顺序，可能出现type3在前、type2在后的无效配对
4. 若数据中存在number为null的记录，t1.number=t2.number会包含null值的匹配

正确的SQL实现 #

SELECT 
    t1.originating_date_time AS type2_time,
    t1.number,
    t1.operation AS type2_op,
    t2.originating_date_time AS type3_time,
    t2.operation AS type3_op
FROM tb1 t1
INNER JOIN tb1 t2
    ON t1.number = t2.number
    AND t1.operation = 'type2'
    AND t2.operation = 'type3'
    AND TIMESTAMPDIFF(SECOND, t1.originating_date_time, t2.originating_date_time) < 7
WHERE 
    t1.operation != 'type1'
    AND t2.operation != 'type1'

代码解释 #

• 用INNER JOIN ON明确关联条件，确保t1是type2、t2是type3，且属于同一number
• TIMESTAMPDIFF(SECOND, ...)直接计算两条记录的时间差（秒数），判断是否小于7，比TIMEDIFF更直观且不易出错
• 字段别名让结果更清晰，便于区分type2和type3的记录

执行此SQL后，会仅返回符合条件的记录对：

type2_time           number  type2_op  type3_time           type3_op
12/27/2022 16:27:07  11232   type2     12/27/2022 16:27:11  type3

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