Mysql:根据最近时间戳查询加入表数据 [英] Mysql: Query to Join Table Data Based On Closest Timestamp

问题描述

我在 Mysql 中有表，我需要能够将基于最近时间戳的数据与匹配行 [可能早于或等于或大于另一行的时间戳 [但最近] 基于用户名].

I have Table in Mysql and I need to be able join the data based on the nearest timestamp in with the matching row [that may be earlier than or equal to or greater than the timestamp[but nearest] of another row based on username].

这是示例数据 Mysql 表:

Here is Sample data Mysql Table:

+----+----------+---------+----------+---------------------+
| Id | userName | Browser | Platform | TS                  |
+----+----------+---------+----------+---------------------+
|  1 | abc      | Firefox | NULL     | 2006-10-05 11:55:45 |
|  2 | xyz      | Chrome  | NULL     | 2007-10-07 12:34:17 |
|  3 | mnp      | Safari  | NULL     | 2008-10-09 08:19:37 |
|  4 | abc      | Safari  | NULL     | 2010-10-13 04:28:14 |
|  5 | abc      | NULL    | Windows  | 2006-01-01 12:02:45 |
|  6 | xyz      | NULL    | Linux    | 2007-01-01 12:01:20 |
|  7 | mnp      | NULL    | MAC      | 2008-01-01 12:02:29 |
|  8 | abc      | NULL    | MAC      | 2010-03-09 13:06:59 |
+----+----------+---------+----------+---------------------+

我需要如下输出:

+----+----------+---------+----------+---------------------+
| Id | userName | Browser | Platform | TS                  |
+----+----------+---------+----------+---------------------+
|  1 | abc      | Firefox | Windows  | 2006-10-05 11:55:45 |
|  2 | xyz      | Chrome  | Linux    | 2007-10-07 12:34:17 |
|  3 | mnp      | Safari  | MAC      | 2008-10-09 08:19:37 |
|  4 | abc      | Safari  | MAC      | 2010-10-13 04:28:14 |
|  5 | abc      | Firefox | Windows  | 2006-01-01 12:02:45 |  
|  6 | xyz      | Chrome  | Linux    | 2007-01-01 12:01:20 |
|  7 | mnp      | Safari  | MAC      | 2008-01-01 12:02:29 |
|  8 | abc      | Safari  | MAC      | 2010-03-09 13:06:59 |
+----+----------+---------+----------+---------------------+

任何人都可以建议获得所需输出所需的 mysql 查询

Can Anyone suggest a mysql query needed to get desired output

推荐答案

如果您使用的是不支持分析功能的早期版本的 mysql:

And in case you are in an earlier version of mysql, which doesn't support analytical functions:

(这应该等同于@Used_By_Already 的另一个答案)

(This should be equivalent to the other answer by @Used_By_Already)

SELECT 
  t2.Id, 
  t2.userName, 
  COALESCE(t2.Browser, t2.other_Browser), 
  COALESCE(t2.Platform, t2.other_Platform),
  t2.TS, 
  t2.other_TS
from (

SELECT 
  t.*,
  IF (t.Id = @prev_id, @rankk:=@rankk+1, @rankk:=0) as rankk,
  @prev_id := t.Id
from (
  select 
    a.Id, a.userName, a.Browser , a.Platform, a.TS, 
    b.Id as other_Id, b.Browser as other_Browser , b.Platform as other_Platform, b.TS as other_TS,
    ABS(TIMESTAMPDIFF(SECOND, a.TS, b.TS)) as time_diff
  from mytable a join mytable b on a.userName = b.userName and a.Id <> b.Id
  order by a.Id, ABS(TIMESTAMPDIFF(SECOND, a.TS, b.TS)))
  t join (select @prev_Id:=NULL, @rankk:=0) c
) t2
 WHERE rankk=0

这篇关于Mysql:根据最近时间戳查询加入表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！