这里的目标是:
1.从EACH商店中获取具有最新日期的行以获取EACH成分。
2.根据此结果,比较价格以找到最便宜的EACH配料商店。
我可以在单独的查询中完成第一个或第二个目标,但不能同时完成。
如何过滤选择内容,然后对先前的结果应用另一个过滤器?
编辑:
我一直无法获得来自MAX和MIN的结果,因为它只是任意获取其余数据。为了避免这种情况,我应该在多列上联接表(我猜)。林不知道这将如何与重复的日期等
我包括了查询及其输出数据的图像。
如果以原料1为例,它存在于三个单独的商店中(一个商店在不同的日期两次)。
在这种情况下,配料1的当前最便宜的价格将是store3。如果日期为2013-05-25的第四行甚至更便宜,由于它已过时,因此它仍然不会“赢”。
(无视品牌名称,在这个问题上他们并不重要。)
将不胜感激您可以提供的任何帮助/输入!
最佳答案
这个问题真的很有趣!
因此,首先,我们从EACH商店获取有关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