如何在MySQL中为每一行的唯一列值选择两条记录？

161

蒸气59：

我有一个像这样的MySQL表：

+----+-----+-------+------+------+-------+---------------------+
| ID | GID | Name  |  p1  | p10  | p100  |      createdAt      |
+----+-----+-------+------+------+-------+---------------------+
|  1 | 100 | Item1 |  150 | 1499 | 10245 | 2020-07-04 12:00:00 |
|  2 | 857 | Item2 | 1047 | 9875 | 90000 | 2020-07-04 12:00:10 |
|  3 | 100 | Item1 |  149 | 1495 | 10245 | 2020-07-04 12:15:00 |
|  4 | 857 | Item2 | 1099 | 9875 | 89999 | 2020-07-04 12:15:10 |
|  5 | 100 | Item1 |  149 | 1495 | 10247 | 2020-07-04 12:30:00 |
|  6 | 857 | Item2 |  970 | 9879 | 89998 | 2020-07-04 12:30:10 |
+----+-----+-------+------+------+-------+---------------------+

我尝试为每个唯一GID值输出p1, p10, p100最近两个的值createdAt

输出示例：

+-----+-------+------+------+-------+---------+----------+-----------+
| GID | Name  |  p1  | p10  | p100  | p1-last | p10-last | p100-last |
+-----+-------+------+------+-------+---------+----------+-----------+
| 100 | Item1 |  149 | 1495 | 10245 |     149 |     1495 |     10247 |
| 857 | Item2 | 1099 | 9875 | 89999 |     970 |     9879 |     89998 |
+-----+-------+------+------+-------+---------+----------+-----------+

我尝试使用子查询来实现我的目标，但对此我并不满意。

谢谢任何能为我提供信息和帮助的人。

戈登·利诺夫（Gordon Linoff）：

您将为此使用lag()：

select gid, name, p1, p10, p100, prev_p1, prev_p10, prev_p100
from (select t.*,
             lag(p1) over (partition by gid order by createdAt) as prev_p1,
             lag(p10) over (partition by gid order by createdAt) as prev_p10,
             lag(p100) over (partition by gid order by createdAt) as prev_p100,
             row_number() over (partition by gid order by createdAt desc) as seqnum
      from t
     ) t
where seqnum = 1;

这是一个db <>小提琴。

子查询返回每列的先前值。外部查询仅过滤每个gid/ name组合的最新行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。