SQL Postgresql 为每个 id 提取最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28085468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql extract last row for each id
提问by Marta
Suppose I've next data
假设我有下一个数据
id date another_info
1 2014-02-01 kjkj
1 2014-03-11 ajskj
1 2014-05-13 kgfd
2 2014-02-01 SADA
3 2014-02-01 sfdg
3 2014-06-12 fdsA
I want for each id extract last information:
我想为每个 id 提取最后的信息：
id date another_info
1 2014-05-13 kgfd
2 2014-02-01 SADA
3 2014-06-12 fdsA
How could I manage that?
我怎么能做到这一点？
回答by a_horse_with_no_name
The most efficient way is to use Postgres' distinct onoperator
最有效的方法是使用 Postgres 的distinct on操作符
select distinct on (id) id, date, another_info
from the_table
order by id, date desc;
If you want a solution that works across databases (but is less efficient) you can use a window function:
如果您想要一个跨数据库工作的解决方案（但效率较低），您可以使用窗口函数：
select id, date, another_info
from (
select id, date, another_info,
row_number() over (partition by id order by date desc) as rn
from the_table
) t
where rn = 1
order by id;
The solution with a window function is in most cases faster than using a sub-query.
在大多数情况下，使用窗口函数的解决方案比使用子查询更快。
回答by Vivek S.
select *
from bar
where (id,date) in (select id,max(date) from bar group by id)
Tested in PostgreSQL,MySQL
在 PostgreSQL、MySQL 中测试
回答by Amal Ts
Group by id and use any aggregate functions to meet the criteria of last record. For example
按 id 分组并使用任何聚合函数来满足最后一条记录的条件。例如
select id, max(date), another_info
from the_table
group by id, another_info
