mysql - Mysql：根据最近的时间戳查询连接表数据

Question

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

这是示例数据 Mysql 表：

+----+----------+---------+----------+---------------------+
| 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 查询吗

标签： mysql

Answer 1

如果你有 MySQL v8 或 MariaDb，它支持lead() over()

CREATE TABLE mytable(
   Id       INT
  ,userName VARCHAR(10)
  ,Browser  VARCHAR(9)
  ,Platform VARCHAR(10)
  ,TS       timestamp
);

INSERT INTO mytable(Id,userName,Browser,Platform,TS) VALUES >     >     >     >     >       (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')

with cte as (
select
*
, coalesce(lead(ts) over(order by ts),current_time) nxt
from mytable
)
select
  id
, username
, coalesce(browser,(select browser from cte where t.ts between cte.ts and cte.nxt limit 1 )) browser
, coalesce(platform,(select platform from cte where t.ts between cte.ts and cte.nxt limit 1 )) platform
, ts
, nxt
from cte t

编号 | 用户名 | 浏览器 | 平台 | ts | 下一个                
-: | :------- | :-------- | :------- | :----------------- | :-----------------
 5 | 美国广播公司 | 空    | 窗户 | 2006-01-01 12:02:45 | 2006-10-05 11:55:45
 1 | 美国广播公司 | 火狐 | 窗户 | 2006-10-05 11:55:45 | 2007-01-01 12:01:20
 6 | xyz | 火狐 | Linux | 2007-01-01 12:01:20 | 2007-10-07 12:34:17
 2 | xyz | 铬 | Linux | 2007-10-07 12:34:17 | 2008-01-01 12:02:29
 7 | mnp | 铬 | 麦克 | 2008-01-01 12:02:29 | 2008-10-09 08:19:37
 3 | mnp | 野生动物园 | 麦克 | 2008-10-09 08:19:37 | 2010-03-09 13:06:59
 8 | 美国广播公司 | 野生动物园 | 麦克 | 2010-03-09 13:06:59 | 2010-10-13 04:28:14
 4 | 美国广播公司 | 野生动物园 | 麦克 | 2010-10-13 04:28:14 | 2018-10-02 08:11:44

db<>在这里摆弄