如何获得每个名称的非最新记录?我希望得到这样的结果:如果一个名字有一条记录,它将被排除,因为它是最新的。如果一个名字有两条记录,我将包括较早的那条。如果一个名字有三条记录,它将包括较老的两条。
如果我有一个这样的表
+-------+---------------------+---------+
| name | date | picture |
+-------+---------------------+---------+
| jose | 2020-12-11 09:27:24 | 1.jpg |
| ned | 2021-12-10 09:27:31 | 20.jpg |
| ned | 2018-12-25 09:27:34 | 55.jpg |
| sid | 2017-12-20 09:28:21 | 21.jpg |
| ned | 2021-12-19 09:27:34 | 22.jpg |
| sid | 2015-12-15 09:28:21 | 66.jpg |
| wade | 2014-12-17 09:28:21 | 88.jgg |
| wade | 2019-12-18 09:28:21 | 11.jpg |
| wade | 2021-12-19 09:28:21 | 10.jpg |
| wade | 2022-12-05 09:28:21 | 20.jpg |
+-------+---------------------+---------+
结果应为
+-------+---------------------+---------+
| name | date | picture |
+-------+---------------------+---------+
| ned | 2021-12-10 09:27:31 | 20.jpg |
| ned | 2018-12-25 09:27:34 | 55.jpg |
| sid | 2015-12-15 09:28:21 | 66.jpg |
| wade | 2014-12-17 09:28:21 | 88.jgg |
| wade | 2019-12-18 09:28:21 | 11.jpg |
| wade | 2021-12-19 09:28:21 | 10.jpg |
+-------+---------------------+---------+
这个问题可以用下面的方法使用窗口函数row_number
来解决:
with ordered as (
select
t.*, row_number() over (partition by name order by date desc) rn
from t
) select * from ordered where rn > 1;
https://sqlize.online/s/TW
row_number
按日期顺序返回数字,最老的记录总是以1编号,之后我们简单地过滤掉这些记录
@slava解决方案的变体使用array_agg
而不是row_number:
SELECT name
, unnest((array_agg(date ORDER BY date DESC))[2:]) AS date
, unnest((array_agg(picture ORDER BY date DESC))[2:]) AS picture
FROM my_table
GROUP BY name
[2:]
排除了数组中与最高日期相关的第一个值,因为数组值为ORDERed BY date DESC
看到dbfiddle