我有一個events帶有列的表：
create table event
(
id bigserial
primary key,
created_at timestamp with time zone default now() not null,
type text not null,
package_id bigint not null,
data jsonb
);
我想通過一個串列來獲取行，package_ids但created_at每個package_id. 因此，例如，如果我有：
id | created_at | type | package_id
1 2022-02-02 14:46:17.906101 00:00 ACCEPTED_TERMINAL_DPT 215
2 2022-02-02 14:50:23.740839 00:00 LOADED_ON_FLIGHT 215
3 2022-02-02 14:50:49.132368 00:00 STORAGE_IN_TERMINAL 215
4 2022-02-02 14:50:57.067934 00:00 HANDED_OVER_TO_DRIVER 215
5 2022-02-02 16:20:51.306371 00:00 CUSTOMS_EXPORT 215
6 2022-02-02 16:21:45.661580 00:00 HELD_IN_CUSTOMS 215
7 2022-02-02 16:36:20.434390 00:00 CUSTOMS_IMPORT 215
10 2022-02-03 14:12:39.751217 00:00 CUSTOMS_EXPORT 241
11 2022-02-03 14:12:42.979952 00:00 HANDED_OVER_TO_DRIVER 241
從這些行中，我只想獲取 2 行（這些是每個包 ID 的最新創建日期的行）：
7 2022-02-02 16:36:20.434390 00:00 CUSTOMS_IMPORT 215
11 2022-02-03 14:12:42.979952 00:00 HANDED_OVER_TO_DRIVER 241
這樣的事情顯然行不通：
select *
from event
where package_id in (215, 241)
group by created_at
所以，我想知道什么樣的查詢可以給我這樣的結果？
uj5u.com熱心網友回復：
按 package_id 和降序 created_at 排序，在 select 中應用Distinct 。
select distinct on (package_id) *
from event
where package_id in (215, 241)
order by package_id, created_at desc;
對 created_at 進行降序排序是因為：
SELECT DISTINCT ON ( expression [, ...] ) 只保留給定運算式 ... 相等的每組行的第一行
uj5u.com熱心網友回復：
這應該對你有用嗎？
with events as (
select
*,
row_number() over (partition by package_id order by created_at desc) as row_number
from event
where package_id in (215, 241)
)
select * from events where row_number = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454920.html
標籤：sql PostgreSQL