Oracle DB多表连接：无需LATERAL与嵌套实现订单限制查询

Oracle 12c及以上版本支持QUALIFY子句，能直接在主查询中过滤窗口函数的计算结果，完美规避嵌套子查询或LATERAL关联的性能问题。以下是针对Northwind数据库需求的具体实现：

SELECT
    c.CustomerID,
    c.CompanyName,
    o.OrderID,
    o.OrderDate,
    p.ProductID,
    p.ProductName,
    od.UnitPrice,
    od.Quantity
FROM
    Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
QUALIFY
    -- 每个客户仅保留最新的3笔订单（订单日期倒序，ID兜底避免同日期排序歧义）
    ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC) <= 3
    -- 每笔订单仅保留单价最高的产品（单价倒序，产品ID兜底处理同价场景）
    AND ROW_NUMBER() OVER (PARTITION BY o.OrderID ORDER BY od.UnitPrice DESC, p.ProductID ASC) = 1
ORDER BY
    c.CustomerID,
    o.OrderDate DESC,
    od.UnitPrice DESC;

方案细节说明 #

• 表关联逻辑：通过CustomerID、OrderID、ProductID依次关联四张表，保持基础关联的简洁清晰。
• QUALIFY子句核心作用：
  1. 第一个窗口函数按客户分组，对订单按日期倒序编号，筛选前3条实现“最新3笔订单”的需求。
  2. 第二个窗口函数按订单分组，对产品按单价倒序编号，筛选第1条实现“每笔订单单价最高产品”的需求。
• 性能与维护优势：
  相比LATERAL的逐行关联，窗口函数采用批量计算逻辑，Oracle优化器可生成更高效的执行计划，适配大数据量场景；同时无需嵌套子查询，SQL结构直观，后续修改和维护成本更低。

版本与场景适配 #

• 若Oracle版本低于12c，QUALIFY子句不可用，此时只能采用嵌套子查询的ROW_NUMBER()方案，但12c及以上版本优先推荐上述写法。
• 若同一订单存在多个单价相同的最高单价产品，可根据业务需求调整ORDER BY后的兜底排序字段（如改为p.ProductName ASC）。

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