如何查询订单商品总价最高的订单？（MySQL与Oracle实现）

问题核心 #

从Orders（订单表，字段id、orderName）和Order_item（订单项表，字段id、partname、price、order_id，order_id关联Orders.id）中，找出订单总价最高的所有订单（若存在多个订单总价相同且为最高值，需全部返回）。

MySQL 实现方式 #

1. 仅返回单个最高总价订单（你的可行语句） #

你提供的第二个语句可正常运行，但仅能返回一个订单（即使存在多个总价并列最高的情况）：

SELECT ord.* 
FROM ORDERS ord 
WHERE ID =  (  
    SELECT o.id 
    FROM ORDERS o
    INNER JOIN order_item oi ON o.id = oi.order_id 
    GROUP BY o.id 
    ORDER BY SUM(oi.price) DESC 
    LIMIT 1
);

2. 返回所有并列最高总价的订单 #

如果需要覆盖多个订单总价相同且为最高的场景，使用以下语句：

SELECT ord.*
FROM ORDERS ord
JOIN (
    -- 先计算每个订单的总价
    SELECT o.id, SUM(oi.price) AS total_price
    FROM ORDERS o
    INNER JOIN order_item oi ON o.id = oi.order_id
    GROUP BY o.id
) order_totals ON ord.id = order_totals.id
-- 筛选出总价等于最高总价的订单
WHERE order_totals.total_price = (
    SELECT MAX(total_price)
    FROM (
        SELECT SUM(oi.price) AS total_price
        FROM order_item oi
        GROUP BY oi.order_id
    ) temp
);

Oracle 实现方式 #

Oracle不支持MySQL的LIMIT语法，需用窗口函数或ROWNUM实现，以下是两种常用方案：

1. 推荐：用RANK()窗口函数（支持返回所有并列最高订单） #

RANK()函数会为所有总价最高的订单标记排名为1，自动处理并列场景：

SELECT ord.id, ord.orderName
FROM ORDERS ord
JOIN (
    SELECT 
        o.id, 
        SUM(oi.price) AS total_price,
        -- 按总价降序排名，相同总价排名一致
        RANK() OVER (ORDER BY SUM(oi.price) DESC) AS price_rank
    FROM ORDERS o
    INNER JOIN order_item oi ON o.id = oi.order_id
    GROUP BY o.id
) order_totals ON ord.id = order_totals.id
WHERE order_totals.price_rank = 1;

2. 仅返回单个最高总价订单 #

若只需返回一个订单（不管是否有并列），可使用ROWNUM，注意必须嵌套子查询（否则会先过滤再排序，导致结果错误）：

SELECT ord.*
FROM ORDERS ord
WHERE ord.id = (
    SELECT o.id
    FROM (
        -- 先排序再取第一条
        SELECT o.id
        FROM ORDERS o
        INNER JOIN order_item oi ON o.id = oi.order_id
        GROUP BY o.id
        ORDER BY SUM(oi.price) DESC
    ) temp
    WHERE ROWNUM = 1
);

关于你第一个语句的问题 #

你第一个语句的错误在于分组逻辑：GROUP BY oi.id, ord.orderName中，ord.orderName是外层表字段，分组逻辑错误；同时按oi.id（订单项ID）分组，会导致每个订单项单独计算价格，最终得到的是单个商品价格最高的订单项对应的订单，而非订单总价最高的订单。

内容的提问来源于stack exchange，提问作者Petr Kostroun