mysql - MySQL 中组的最新条目

Question

我有两个表:商品和价格

prices 是商品不同时间戳的价格集合(在本例中为 24)

项目包含所有项目的描述

我现在想要查询每个商品的名称、销售价格、购买价格、报价数量、需求数量以及最新价格的两个 ID。

我当前的解决方案

SELECT items.id as id, items.icon as icon, items.name AS name, prices.sell as sell, prices.buy as buy, prices.demand as demand, prices.offer as offer, prices.timestamp as timestamp
FROM items JOIN prices ON prices.itemID = items.id
WHERE buy < 100000
  AND sell < 100000
  AND demand > 10000
  AND offer > 10000
  AND timestamp IN (SELECT MAX(timestamp) FROM prices AS p WHERE p.id = prices.id)
GROUP BY items.id
ORDER BY sell / buy
DESC LIMIT 25

根本不起作用(行似乎是随机的，但它们肯定不是最新的)。

Answer 1

看完我的蹩脚照片后，我意识到解决方案就在我面前。我不需要分组，只需使用 items.lastupdate 来选择正确的价格行

FROM items JOIN prices ON prices.itemID = items.id AND prices.timestamp = items.lastupdate