mySQL查询找到24小时内最低的 "active"价格

Question

请帮我摆脱我的头痛..提前我为我的糟糕语言道歉，无论是英语还是mysql。希望有人能理解这个问题..:)

我有一个数据库，任何人都可以记录各个商店中各种产品的价格。以下查询是一个半理论示例，可能根本不起作用，但它是为了说明我的挑战而进行翻译的。该查询应该找到过去 24 小时内给定 $product 的最低注册价格:

SELECT img, chain.chainid, chain.chainname, shops.shopid, shoplocation, shopname, prices.price, DATE_FORMAT( price.timestamp, '%e. %M %Y, %H:%i' ) AS time
FROM shops
INNER JOIN prices ON shops.shopid = prices.shopid
INNER JOIN chain ON shop.chainid = chain.chainid
WHERE prices.product = '$product'
AND timestamp > SUBTIME( NOW( ) , '24:0:0.000000' )
ORDER BY price, timestamp DESC
LIMIT 1;

即使今天早上最便宜的商店在白天提高了价格(假设我的表中有两个价格)并且另一家商店现在是最便宜的，我如何确保我仍然找到价格最低的商店？

Answer 1

您重写的查询:

SELECT
  img,
  chain.chainid,
  chain.chainname,
  shops.shopid,
  shoplocation,
  shopname,
  prices.price,
  DATE_FORMAT(prices.timestamp, '%e. %M %Y, %H:%i') AS time
FROM shops
  INNER JOIN (
    SELECT shopid, MAX(timestamp) AS timestamp
    FROM prices
    WHERE prices.product = '$product'
      AND timestamp > SUBTIME(NOW(), '24:0:0.000000')
             ) prices_tmp
    ON prices_tmp.shopid = shops.shopid
  INNER JOIN prices
    ON shops.shopid = prices.shopid
      AND prices.timestamp = prices_tmp.timestamp
  INNER JOIN chain
    ON shop.chainid = chain.chainid
WHERE prices.product = '$product'
ORDER BY price DESC
LIMIT 1;

注意使用prices_tmp子查询。但我不确定它是否有效。

一些解释:在所选每个商店最新价格时间戳的子查询中，使用 prices_tmp 进行inner join 过滤旧价格，然后将 order by 放入最低价格位于顶部。