我在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查询来获得所需的输出吗
2条答案
按热度按时间x9ybnkn61#
如果您使用的是早期版本的mysql,它不支持分析函数:
(这应该等同于@used\u by \u already的另一个答案)
dfuffjeb2#
如果您有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
id | username | browser | platform | ts | nxt
-: | :------- | :------ | :------- | :------------------ | :------------------
5 | abc | null | Windows | 2006-01-01 12:02:45 | 2006-10-05 11:55:45
1 | abc | Firefox | Windows | 2006-10-05 11:55:45 | 2007-01-01 12:01:20
6 | xyz | Firefox | Linux | 2007-01-01 12:01:20 | 2007-10-07 12:34:17
2 | xyz | Chrome | Linux | 2007-10-07 12:34:17 | 2008-01-01 12:02:29
7 | mnp | Chrome | MAC | 2008-01-01 12:02:29 | 2008-10-09 08:19:37
3 | mnp | Safari | MAC | 2008-10-09 08:19:37 | 2010-03-09 13:06:59
8 | abc | Safari | MAC | 2010-03-09 13:06:59 | 2010-10-13 04:28:14
4 | abc | Safari | MAC | 2010-10-13 04:28:14 | 2018-10-02 08:11:44