使用SubQueries查找在24小时窗口内两次请求确认消息的用户的ID,(MySQL)

hl0ma9xz  于 12个月前  发布在  Mysql
关注(0)|答案(1)|浏览(60)

enter image description here
| 时间戳|动作| action |
| --|--| ------------ |
| 2020-03-27 14:33:06|超时| timeout |
| 2020-04-02 08:53:15|确认| confirmed |
| 2020-04-12 23:29:44|确认| confirmed |
| 2020-05-16 22:20:24|超时| timeout |
| 2020-07-06 17:39:03|超时| timeout |
| 2020-09-28 06:27:34|超时| timeout |
| 2020-01-12 14:54:19|确信| confired |
我能够使用自连接解决
从确认c1中选择不同的c1.user_id=c2.user_id和c1.time_stamp上的确认c2!=c2.time_stamp其中TIMESTAMPDIFF(SECOND,c1.time_stamp,c2.time_stamp)<= 86400和TIMESTAMPDIFF(SECOND,c1.time_stamp,c2.time_stamp)>=0按1排序;

0s0u357o

0s0u357o1#

使用exists子查询检查任何confirmed操作是否在24小时内有后续confirmed操作:

select distinct c.user_id from confirmations c
where c.action = 'confirmed' and exists (select 1 from confirmations c1 
   where c1.user_id = c.user_id and c1.time_stamp > c.time_stamp 
        and c1.time_stamp <= c.time_stamp + interval 24 hour)

字符串
Try it online!

相关问题