sql - 如何使用附加列选择最大日期

Question

给定以下数据库结构(它只是一个演示结构):

order:
| id | orderedat |

order_item:
| id | id_order | id_article | price | currency |

我要获取的是每件商品的最新订单价格:

| id_article | orderedat | price | currency |

我写下这条语句是为了实现目标:

WITH "last_ordered" AS (   
  SELECT "oi"."id_article", "oi"."id" as "id_oi", MAX("o"."orderedat")   
  FROM "order" "o", "order_item" "oi"   
  WHERE ("oi"."id_order" = "o"."id")            
  GROUP BY "oi"."id", "oi"."id_id_article"    
)   
SELECT "oi"."id_article", "o"."orderedat", "oi"."price", "oi"."currency"   
FROM "order_item" "oi"   
INNER JOIN "last_ordered" ON ("oi"."id" = "last_ordered"."id_oi")   
INNER JOIN "order" "o" ON ("oi"."id_order" = "o"."id") 

但是，分组依据是错误的，因为它为文章链接到的每个 order_item 返回一行。另一方面，“oi.id”列必须出现在 GROUP BY 子句中。我不知道如何解决这个问题。有人可以帮帮我吗？

也许这个问题有另一种解决方案。

提前致谢:)

Answer 1

select distinct on (id_article)
    id_article, orderedat, price, currency
from
    order o
    inner join
    order_item oi on o.id = oi.id_order
order by id_article, orderedat desc

检查 distinct on :

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.