提问者:小点点

如何在MySQL中为每个唯一列值选择两条记录作为一行?


我有一个这样的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,这些P1,p10,P100是在创建的两个最近的

输出示例:

+-----+-------+------+------+-------+---------+----------+-----------+
| 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 |
+-----+-------+------+------+-------+---------+----------+-----------+

我试图使用子查询来实现我的目标,但我并不满意。

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


共2个答案

匿名用户

您可以使用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组合的最新行。

匿名用户

没有窗口函数,这是不容易和不优雅的。
这是一种方法,它涉及自连接,两个级别的聚合和条件聚合:

select t.gid, t.name,
  max(case when c.counter = 1 then t.p1 end) p1,
  max(case when c.counter = 1 then t.p10 end) p10,
  max(case when c.counter = 1 then t.p100 end) p100,
  max(case when c.counter = 0 then t.p1 end) p1_last,
  max(case when c.counter = 0 then t.p10 end) p10_last,
  max(case when c.counter = 0 then t.p100 end) p100_last
from tablename t inner join (
  select t1.gid, t1.createdat, count(t2.createdat) counter
  from tablename t1 left join tablename t2
  on t2.gid = t1.gid and t1.createdat < t2.createdat 
  group by t1.gid, t1.createdat
  having count(t2.createdat) <= 1
) c on c.gid = t.gid and c.createdat = t.createdat
group by t.gid, t.name

请参阅演示。
结果:

| 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     |