每个产品的最低/最高价格(查询) [英] MIN/MAX price for each product (query)

问题描述

选择数据库中每种产品的最低/最高价格.

Select MIN/MAX price of each product in database.

我只能获取具有指定标识符的产品.

I am able to get only products with specified identifiers.

我正在使用 MySQL 并且我有以下查询:

I'm using MySQL and I have the follow query:

SELECT (MIN(`map`.`Product_Price`)) as `minProductPrice`,
    (MAX(`map`.`Product_Price`)) as `maxProductPrice`,
    `pr`.`Product_Name` as `productName`
FROM `bm_market_products` as `map`
JOIN `bm_products` as `pr`
JOIN `bm_markets` as `ma`
WHERE `map`.`Product_Id` = 1
AND `map`.`Product_Id` = `pr`.`Product_Id`

我返回的是 minProductPrice、maxProductPrice 和 productName.

My return is the minProductPrice, maxProductPrice and productName.

感谢您的帮助.上面的两个答案都是正确的——但我选择了@GordonLinoff 的答案，因为我认为它会更有用，初学者更喜欢——但真的要感谢你们两个.最终查询:

Thank you about your help. The both answers above are right — but I chose @GordonLinoff answer's as accepted because I think it will be more useful and enjoyed by beginners — but really thanks to you both guys. The final query:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
group by `map`.`Product_Id`

干杯！

推荐答案

首先，当你使用join时，你应该总是有一个on 子句，即使 MySQL 不需要这个.如果你想要一个cross join，那就明确说明它.

First, when you use join, you should always have an on clause, even though MySQL does not require this. If you want a cross join, then be explicit about it.

第二，在查询中根本不使用 tm_markets 表.不需要，所以删除它.

Second, you don't use the tm_markets table at all in the query. It is not needed, so remove it.

结果查询应该可以工作:

The resulting query should work:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
WHERE `map`.`Product_Id` = 1 

因为您只选择一种产品，group by 可能没有必要.不过，您可以考虑这样做:

Because you are only choosing one product, a group by is probably not necessary. You might consider this, however:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
group by `map`.`Product_Id`

这将返回所有产品的信息.

That will return the information for all products.

这篇关于每个产品的最低/最高价格(查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！