我的查询确实返回了两列中的最低价格（price\u base，price\u special），但没有返回与找到的最低价格相对应的正确的store\u id。
我的问题：

SELECT grocery_item.id, grocery_item.category,
grocery_category.name AS cat, grocery_item.name AS itemName,
MIN( if( grocery_price.price_special>0,
grocery_price.price_base)) AS price,
grocery_price.store_id,
grocery_store.name AS storeName
FROM grocery_item
LEFT JOIN grocery_category ON
grocery_category.id=grocery_item.category
LEFT JOIN grocery_price
ON grocery_price.item_id = grocery_item.id
LEFT JOIN grocery_store 
ON grocery_store.id=grocery_price.store_id   
WHERE grocery_price.selection='no'
AND buy='yes'
GROUP BY grocery_price.item_id
ORDER BY store_id, grocery_item.category, grocery_item.name

返回以下内容：

ID  category    cat     itemName       price    store_id    storeName
92  3   Bread/Bakery    Arnold Bread    2.14    1           Food Lion

但杂货价格表包含以下信息：

item_id price_base  price_special   store_id
92      4.29        2.14            9
92      3.99        0.00            1

所以我需要返回的store\u id是9（返回的storename不是food lion）
编辑：基于Uuerdo评论的工作查询（谢谢！）

SELECT minP.item_id, gi.category, gc.name AS cat,
gi.name as itemName,  gp.store_id, 
gs.name AS storeName, minP.price
FROM
(SELECT p.item_id, MIN(IF(p.price_special >0,
p.price_special,p.price_base)) AS price
FROM grocery_item AS i
INNER JOIN grocery_price AS p ON (i.id = p.item_id)
WHERE i.buy = 'yes'
GROUP BY p.item_id) AS minP
INNER JOIN grocery_item AS gi ON minP.item_id = gi.id
INNER JOIN grocery_category AS gc on gi.category = gc.id 
LEFT JOIN grocery_price AS gp
ON minP.price = IF(gp.price_special > 0,
gp.price_special,gp.price_base)
AND gp.item_id = gi.id
INNER JOIN grocery_store AS gs ON gp.store_id = gs.id
GROUP BY gi.id
ORDER BY gs.id, gi.category,gi.name

SELECT minP.item_id
, gi.name
, gi.category, gc.category_name AS cat, gi.Name as itemName, gi.buy
, gp.store_id, gp.name AS storeName
, minP.price
FROM ([the query above]) AS minP
INNER JOIN grocery_item AS gi ON minP.item_id = gi.id
INNER JOIN grocery_category AS gc on gi.category = gc.grocery_category_id
/* Guessing on this join since grocery_category_id
was not qualified with it's table name */
INNER JOIN grocery_price AS gp
ON minP.price = IF(gp.price_special > 0,gp.price_special,gp.price_base)
/* Alternatively: ON minP.price IN (gp.price_special, gp.price_base)
... though this could cause false positives if the minP.price is 0
from one store's base price being "free"
*/
INNER JOIN grocery_store AS gs ON gp.store_id = gs.id
;

1条答案

按热度按时间

kse8i1jr1#

为未分组、未聚合字段返回的值是（有效地）从分组字段的值遇到的值中随机选择的。大多数rdbms甚至不认为这样的查询有效，甚至更新版本的mysql也默认不允许这样的查询。
在像您这样的情况下，需要与聚合结果相关联的非分组值（在这种情况下为min）；聚合查询必须转换为子查询，子查询可以联接回聚合表，以查找与聚合值对应的源行。
编辑：基本上，你需要稍微不同地看待问题。您当前正在查找某个商品和该商品所列商店的最低价格；您需要找到商品的最低价格，并使用该价格找到以该价格销售该商品的商店。
这将为标记为“购买”的商品提供最低价格：

SELECT p.item_id, MIN(IF(p.price_special > 0,p.price_special,p.price_base)) AS price
FROM grocery_item AS i
INNER JOIN grocery_price AS p ON (i.id = p.item_id)
WHERE i.buy = 'yes'
GROUP BY p.item_id

然后你就可以得到剩下的结果：

SELECT minP.item_id
   , gi.name
   , gi.category, gc.category_name AS cat, gi.Name as itemName, gi.buy
   , gp.store_id, gp.name AS storeName
   , minP.price
FROM ([the query above]) AS minP
INNER JOIN grocery_item AS gi ON minP.item_id = gi.id
INNER JOIN grocery_category AS gc on gi.category = gc.grocery_category_id 
    /* Guessing on this join since grocery_category_id 
       was not qualified with it's table name */
INNER JOIN grocery_price AS gp 
   ON minP.price = IF(gp.price_special > 0,gp.price_special,gp.price_base)
   /* Alternatively: ON minP.price IN (gp.price_special, gp.price_base) 
      ... though this could cause false positives if the minP.price is 0 
          from one store's base price being "free"
   */
INNER JOIN grocery_store AS gs ON gp.store_id = gs.id
;

展开查看全部

赞(0）回复(0）举报 2021-06-21

如何从两列中获得最小值以及相应的id？

1条答案

相关问题

热门标签

最新问答