MySQL三表关联查询指定商品对应订单的商品最高/最低价，优先取折扣价

实现逻辑 #

• 第一步：从ORDER_PRODUCTS表过滤得到所有包含指定商品的订单ID集合，避免无关订单参与计算
• 第二步：关联订单商品明细与商品基础表，通过IFNULL()函数实现价格优先级判断：优先取折扣价，折扣价为空时取商品原价
• 第三步：对每个订单内的商品按实际价格排序，分别取价格最低、最高的商品信息，按要求拼接输出字段

完整查询代码 #

-- 定义传入的指定商品ID参数，可根据实际需求修改
SET @target_product_id = 1;

WITH target_orders AS (
    -- 筛选包含目标商品的所有订单
    SELECT DISTINCT order_id
    FROM ORDER_PRODUCTS
    WHERE product_id = @target_product_id
),
order_actual_prices AS (
    -- 计算每个订单下所有商品的实际结算价格
    SELECT
        op.order_id,
        op.product_id,
        op.discount_price,
        IFNULL(op.discount_price, p.product_price) AS actual_price
    FROM ORDER_PRODUCTS op
    JOIN target_orders t ON op.order_id = t.order_id
    JOIN PRODUCTS p ON op.product_id = p.product_id
),
price_ranks AS (
    -- 对每个订单内的商品按价格排序，标记最低、最高价格的商品
    SELECT
        order_id,
        product_id,
        actual_price,
        discount_price,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY actual_price ASC) AS rn_min,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY actual_price DESC) AS rn_max
    FROM order_actual_prices
)
-- 关联得到每个订单的最低、最高价格信息
SELECT
    min_p.order_id,
    CONCAT(
        min_p.actual_price,
        '(p_id=', min_p.product_id, ')',
        IF(min_p.discount_price IS NOT NULL, '(discount price)', '')
    ) AS min_price,
    CONCAT(
        max_p.actual_price,
        '(p_id=', max_p.product_id, ')',
        IF(max_p.discount_price IS NOT NULL, '(discount price)', '')
    ) AS max_price
FROM price_ranks min_p
JOIN price_ranks max_p 
    ON min_p.order_id = max_p.order_id 
    AND min_p.rn_min = 1 
    AND max_p.rn_max = 1
ORDER BY min_p.order_id;

兼容MySQL 5.x及更低版本的改写（无CTE版本） #

如果你的MySQL版本不支持公共表表达式（CTE），可以用子查询实现相同逻辑：

SET @target_product_id = 1;

SELECT
    min_p.order_id,
    CONCAT(
        min_p.actual_price,
        '(p_id=', min_p.product_id, ')',
        IF(min_p.discount_price IS NOT NULL, '(discount price)', '')
    ) AS min_price,
    CONCAT(
        max_p.actual_price,
        '(p_id=', max_p.product_id, ')',
        IF(max_p.discount_price IS NOT NULL, '(discount price)', '')
    ) AS max_price
FROM (
    SELECT
        op.order_id,
        op.product_id,
        op.discount_price,
        IFNULL(op.discount_price, p.product_price) AS actual_price,
        @cur_rank_min := IF(@pre_order_min = op.order_id, @cur_rank_min + 1, 1) AS rn_min,
        @pre_order_min := op.order_id
    FROM ORDER_PRODUCTS op
    JOIN (
        SELECT DISTINCT order_id FROM ORDER_PRODUCTS WHERE product_id = @target_product_id
    ) t ON op.order_id = t.order_id
    JOIN PRODUCTS p ON op.product_id = p.product_id
    ORDER BY op.order_id, IFNULL(op.discount_price, p.product_price) ASC
) min_p
JOIN (
    SELECT
        op.order_id,
        op.product_id,
        op.discount_price,
        IFNULL(op.discount_price, p.product_price) AS actual_price,
        @cur_rank_max := IF(@pre_order_max = op.order_id, @cur_rank_max + 1, 1) AS rn_max,
        @pre_order_max := op.order_id
    FROM ORDER_PRODUCTS op
    JOIN (
        SELECT DISTINCT order_id FROM ORDER_PRODUCTS WHERE product_id = @target_product_id
    ) t ON op.order_id = t.order_id
    JOIN PRODUCTS p ON op.product_id = p.product_id
    ORDER BY op.order_id, IFNULL(op.discount_price, p.product_price) DESC
) max_p ON min_p.order_id = max_p.order_id AND min_p.rn_min =1 AND max_p.rn_max =1
ORDER BY min_p.order_id;

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