Postgresql
查找 2 個最新狀態符合條件的所有項目
我在 PostgreSQL 數據庫中有一個表,如下所示:
ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ...
我想查詢最後兩個狀態為“已過期”和“待處理”的所有項目,如上面範例中顯示的那樣 - 最新的“待處理”和之前的“已過期”。
您可以這樣做以避免需要將行號硬編碼到查詢中:
select * from t as a where status = 'pending' -- must be pending and exists (select * from t as b where status = 'expired' -- must have expired and a.itemfk = b.itemfk and b.id = (select max(c.id) -- must be the previous entry before pending from t as c where c.itemfk = b.itemfk and c.id < a.id) -- must have expired before pending );
此方法執行查找以
id
在條目處於掛起狀態之前獲取該條目的最大值,然後檢查它是否是標記為 的行expired
。