使用子查询找出24小时内两次请求确认消息的用户ID（MySQL）

数据表结构与示例数据 #

注：表中confired应为拼写错误，以下SQL统一按confirmed处理

已实现的自连接方案 #

select distinct c1.user_id
from confirmations c1
join confirmations c2
on c1.user_id=c2.user_id and c1.time_stamp!=c2.time_stamp
where TIMESTAMPDIFF(SECOND,c1.time_stamp,c2.time_stamp)<= 86400 and TIMESTAMPDIFF(SECOND,c1.time_stamp,c2.time_stamp)>=0
order by 1;

子查询实现方案 #

方案1：EXISTS子查询（直观关联） #

通过检查每条确认记录是否存在同用户的另一条确认记录，且时间间隔在24小时内：

SELECT DISTINCT c.user_id
FROM confirmations c
WHERE c.action = 'confirmed'
  AND EXISTS (
    SELECT 1
    FROM confirmations c2
    WHERE c2.user_id = c.user_id
      AND c2.action = 'confirmed'
      AND c2.time_stamp != c.time_stamp
      AND TIMESTAMPDIFF(SECOND, c.time_stamp, c2.time_stamp) BETWEEN 0 AND 86400
  )
ORDER BY c.user_id;

方案2：窗口函数子查询（高效计算时间差） #

先通过窗口函数获取每个用户上一条确认记录的时间，再筛选时间间隔符合条件的用户：

SELECT DISTINCT user_id
FROM (
  SELECT 
    user_id,
    time_stamp,
    LAG(time_stamp) OVER (PARTITION BY user_id ORDER BY time_stamp) AS prev_confirmed_time
  FROM confirmations
  WHERE action = 'confirmed'
) AS sub
WHERE prev_confirmed_time IS NOT NULL
  AND TIMESTAMPDIFF(SECOND, prev_confirmed_time, time_stamp) <= 86400
ORDER BY user_id;

说明 #

• 两个方案都先过滤了action = 'confirmed'的记录，避免无效的timeout或拼写错误数据干扰结果
• 方案1逻辑直接，适合新手理解；方案2利用窗口函数减少关联次数，在数据量大时性能更优
• 示例数据中仅user_id=1满足24小时内有两次确认请求的条件

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