每个产品的最低/最高价格(查询) [英] 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屋!