我正在使用MySQL数据库,在那里我必须使用表。包含唯一产品的产品表和包含每个产品的多个价格的价格表。
今天,我使用以下查询,它将返回每种产品价格表中存在的不同价格的行:
SELECT product.id as id, price.unit_price as price
FROM product
INNER JOIN price
ON price.product_id=product.id
WHERE category_id = 234
如何将此查询更改为只为价格表中价格最低的每个产品(price.unit_price(返回一行?是否可以只使用一个查询?
这可能是最简单的方法:
SELECT product.id as id, min(price.unit_price) as price
FROM product
INNER JOIN price
ON price.product_id=product.id
WHERE category_id = 234
group by product.id
一个简单的方法使用窗口函数:
SELECT p.id as id, pr.unit_price as price
FROM product p INNER JOIN
(SELECT pr.*,
ROW_NUMBER() OVER (PARTITION BY pr.product_id ORDER BY pr.unit_price DESC) as seqnum
FROM price pr
) pr
ON pr.product_id = p.id AND seqnum = 1
WHERE p.category_id = 234;
这允许您从任一表中引入附加列。否则,只需使用聚合:
SELECT p.id as id, MIN(pr.unit_price) as price
FROM product p INNER JOIN
price pr
ON pr.product_id = p.id
WHERE p.category_id = 234;