MySQL再次过滤结果

Question

这里的目标是:1. 从 EACH 商店中获取每种成分的最新日期的行。2. 根据这个结果，比较价格以找到每种成分最便宜的商店。

我可以在单独的查询中完成第一个或第二个目标，但不能同时完成。我如何过滤掉一个选择，然后对之前的结果应用另一个过滤器？

编辑:我从 MAX 和 MIN 获得的结果一直有问题，因为它只是任意获取其余数据。为了避免这种情况，我应该在多列上连接表(我猜)。我不确定这将如何处理重复日期等。

我已经包含了查询及其输出数据的图像。

如果我们以 ingredient1 为例，它存在于三个独立的商店中(在不同的日期在一个商店中两次)。

在这种情况下，成分 1 的当前最低价格是商店 3。如果日期为 2013-05-25 的第四行更便宜，它仍然不会因为过时而“获胜”。(忽略品牌名称，它们在这个问题中并不重要。)

非常感谢您提供的任何帮助/意见!

Answer 1

这个问题真有意思!

因此，首先，我们从 EACH 商店获取每种成分的最新日期行。 (每个商店的最新日期可能不同。)然后，我们比较每家商店的价格(无论日期如何)以找到每种成分的最低价格。

下面的查询很好地使用了 GROUP_CONCAT 函数。这是一个 SO question关于函数的使用。

SELECT
   i.name as ingredient_name
  , MIN(store_price.price) as price
  , SUBSTRING_INDEX(
    GROUP_CONCAT(store_price.date ORDER BY store_price.price),
    ',',
    1
    ) as date
  , SUBSTRING_INDEX(
    GROUP_CONCAT(s.name ORDER BY store_price.price),
    ',',
    1
    ) as store_name
  , SUBSTRING_INDEX(
    GROUP_CONCAT(b.name ORDER BY store_price.price),
    ',',
    1
    ) as brand_name
FROM
  ingredient i
JOIN
(SELECT
  ip.ingredient_id as ingredient_id
  , stip.store_id as store_id
  , btip.brand_id as brand_id
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.ingredient_price_id ORDER BY ip.date DESC),
    ',',
    1
    ), UNSIGNED INTEGER) as ingredient_price_id
  , MAX(ip.date) as date
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.price ORDER BY ip.date DESC),
    ',',
    1
    ), DECIMAL(5,2)) as price
FROM ingredient_price ip
JOIN store_to_ingredient_price stip ON ip.ingredient_price_id = stip.ingredient_price_id
JOIN brand_to_ingredient_price btip ON ip.ingredient_price_id = btip.ingredient_price_id
GROUP BY 
  ip.ingredient_id
  , stip.store_id) store_price
ON i.ingredient_id = store_price.ingredient_id
JOIN store s ON s.store_id = store_price.store_id
JOIN brand b ON b.brand_id = store_price.brand_id
GROUP BY
  store_price.ingredient_id;

您可以查看此 SQL Fiddle 上的实现.

下面忽略品牌的版本略小:

SELECT
   i.name as ingredient_name
  , MIN(store_price.price) as price
  , SUBSTRING_INDEX(
    GROUP_CONCAT(store_price.date ORDER BY store_price.price),
    ',',
    1
    ) as date
  , SUBSTRING_INDEX(
    GROUP_CONCAT(s.name ORDER BY store_price.price),
    ',',
    1
    ) as store_name
FROM
  ingredient i
JOIN
(SELECT
  ip.ingredient_id as ingredient_id
  , stip.store_id as store_id
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.ingredient_price_id ORDER BY ip.date DESC),
    ',',
    1
    ), UNSIGNED INTEGER) as ingredient_price_id
  , MAX(ip.date) as date
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.price ORDER BY ip.date DESC),
    ',',
    1
    ), DECIMAL(5,2)) as price
FROM ingredient_price ip
JOIN store_to_ingredient_price stip ON ip.ingredient_price_id = stip.ingredient_price_id
GROUP BY 
  ip.ingredient_id
  , stip.store_id) store_price
ON i.ingredient_id = store_price.ingredient_id
JOIN store s ON s.store_id = store_price.store_id
GROUP BY
  store_price.ingredient_id;

引用资料: Simulating First/Last aggregate functions in MySQL