从链接表中获取最小、最大值

Question

我在MYSQL数据库中有3个表

订单

order_id | order_date
-------------------------
1     | 2021-09-20    
2     | 2021-09-21 

产品

product_id | product_price
-------------------------
1     | 30    
2     | 34
3     | 39
4     | 25  

ORDER_PRODUCTS

product_id | order_id
-------------------------
1     | 1    
2     | 1 
1     | 2    
4     | 2 

现在我想知道特定订单中所有产品的最小和最大价格，当我通过order_id给出特定产品id group时。

,

order_id | min_price         | max_price
-----------------------------------------
1  | 30(p_id=1)        | 34(p_id=2)
2  | 25(p_id=4)        | 30(p_id=1)

Answer 1

您可以使用以下命令:

SELECT
op.order_id,
MIN(p.product_price) as min_price,
MAX(p.product_price) as max_price
FROM
ORDER_PRODUCTS op
INNER JOIN
PRODUCTS p ON op.product_id = p.product_id
GROUP BY 
op.order_id

编辑1

因为它属于

现在我想知道a中所有产品的最小和最大价格特定的顺序

你可以使用where子句只考虑一个特定的顺序

SELECT
op.order_id,
MIN(p.product_price) as min_price,
MAX(p.product_price) as max_price
FROM
ORDER_PRODUCTS op
WHERE
op.order_id = <insert order id here>
INNER JOIN
PRODUCTS p ON op.product_id = p.product_id
GROUP BY 
op.order_id

或者如果您想确定具有特定产品的订单的最小和最大价格，您可以修改where子句，如下所示使用带有子查询

的IN

SELECT
op.order_id,
MIN(p.product_price) as min_price,
MAX(p.product_price) as max_price
FROM
ORDER_PRODUCTS op
WHERE
op.order_id IN (
SELECT inc.order_id
FROM ORDER_PRODUCTS inc
WHERE inc.product_id = <insert product id here>
)
INNER JOIN
PRODUCTS p ON op.product_id = p.product_id
GROUP BY 
op.order_id

或者简单地使用带有case语句的having子句来过滤

SELECT
op.order_id,
MIN(p.product_price) as min_price,
MAX(p.product_price) as max_price
FROM
ORDER_PRODUCTS op
INNER JOIN
PRODUCTS p ON op.product_id = p.product_id
GROUP BY 
op.order_id
HAVING 
SUM(
CASE WHEN op.product_id = <insert product id here> THEN 1 END
) > 0

NB。测试时请将<insert product id here>和<insert order id here>替换为实际值

查看工作演示

让我知道这是否适合你。