MySQL从关联表查询分组订单对应商品的最低、最高价格实现

版本1：MySQL 8.0+ 支持窗口函数（推荐，性能更优） #

WITH order_product_price_rank AS (
    SELECT 
        op.order_id,
        p.product_id,
        p.product_price,
        -- 同订单内按价格升序排序，取第一位为最低价格
        ROW_NUMBER() OVER(PARTITION BY op.order_id ORDER BY p.product_price ASC) AS rn_low,
        -- 同订单内按价格降序排序，取第一位为最高价格
        ROW_NUMBER() OVER(PARTITION BY op.order_id ORDER BY p.product_price DESC) AS rn_high
    FROM ORDER_PRODUCTS op
    INNER JOIN PRODUCTS p ON op.product_id = p.product_id
    -- 如需指定商品ID，取消下面注释并替换为目标ID列表
    -- WHERE p.product_id IN (1,2,4)
)
SELECT 
    order_id,
    CONCAT(MAX(IF(rn_low = 1, product_price, NULL)), '(p_id=', MAX(IF(rn_low = 1, product_id, NULL)), ')') AS min_price,
    CONCAT(MAX(IF(rn_high = 1, product_price, NULL)), '(p_id=', MAX(IF(rn_high = 1, product_id, NULL)), ')') AS max_price
FROM order_product_price_rank
GROUP BY order_id;

版本2：兼容MySQL 5.x 旧版本（子查询实现） #

SELECT 
    op.order_id,
    CONCAT(
        MIN(p.product_price), 
        '(p_id=', 
        (SELECT op_sub.product_id 
         FROM ORDER_PRODUCTS op_sub 
         INNER JOIN PRODUCTS p_sub ON op_sub.product_id = p_sub.product_id 
         WHERE op_sub.order_id = op.order_id 
         ORDER BY p_sub.product_price ASC LIMIT 1),
        ')'
    ) AS min_price,
    CONCAT(
        MAX(p.product_price), 
        '(p_id=', 
        (SELECT op_sub.product_id 
         FROM ORDER_PRODUCTS op_sub 
         INNER JOIN PRODUCTS p_sub ON op_sub.product_id = p_sub.product_id 
         WHERE op_sub.order_id = op.order_id 
         ORDER BY p_sub.product_price DESC LIMIT 1),
        ')'
    ) AS max_price
FROM ORDER_PRODUCTS op
INNER JOIN PRODUCTS p ON op.product_id = p.product_id
-- 如需指定商品ID，取消下面注释并替换为目标ID列表
-- WHERE p.product_id IN (1,2,4)
GROUP BY op.order_id;

注意说明 #

• 本需求未用到ORDER表的日期字段，所以没有关联该表，如有订单维度的筛选条件可自行关联ORDER表添加过滤逻辑，注意ORDER是MySQL保留关键字，作为表名使用时需要加反引号包裹为`ORDER`。
• 若同个订单下存在多个商品价格相同且为最值的场景，上述查询默认返回排序后的第一个商品ID，若需返回所有对应ID，可将ROW_NUMBER替换为RANK窗口函数，搭配GROUP_CONCAT调整字符串拼接逻辑即可。

内容的提问来源于stack exchange，提问作者Achyuth Kodali