如何使用IN从PostgreSQL中的表中获取唯一的最新项目

Question

场景

+--------+-------+------------+
|  item  | price |    date    |
+--------+-------+------------+
| apple  | 31    | 2019-09-11 |
| banana | 11    | 2019-09-11 |
| banana | 13.23 | 2019-10-12 |
| apple  | 30.1  | 2019-10-12 |
| banana | 12.77 | 2019-10-31 |
| apple  | 32    | 2019-11-28 |
| banana | 15.6  | 2019-11-02 |
| banana | 12.8  | 2019-12-18 |
| apple  | 33.45 | 2020-01-12 |
| banana | 16.78 | 2020-01-21 |
| apple  | 31.5  | 2020-01-27 |
| banana | 14.4  | 2020-02-17 |
| banana | 13.2  | 2020-03-12 |
+--------+-------+------------+

我有一个价格表，我正试图使用以下SQL查询从中获取100多种水果中香蕉和苹果的价格

SELECT * 
FROM prices
WHERE item IN ('apple','banana')
ORDER BY date DESC
LIMIT 2;

问题

这个查询返回了整张表的最后两个价格，这两个价格都是香蕉的。然而，我想要苹果和香蕉的最新价格

预期

+--------+-------+------------+
| apple  | 31.5  | 2020-01-27 |
| banana | 13.2  | 2020-03-12 |
+--------+-------+------------+

电流

+--------+-------+------------+
| banana | 14.4  | 2020-02-17 |
| banana | 13.2  | 2020-03-12 |
+--------+-------+------------+

我怎样才能做到这一点？

Answer 1

使用distinct on:

SELECT DISTINCT ON (p.item) p.* 
FROM prices p
WHERE item IN ('apple', 'banana')
ORDER BY p.item, p.date DESC