SQL在WHERE中使用MAX()查询单笔最大订单对应客户姓名问题

一、现有SQL正确性判断 #

如果你的业务场景中所有ORDERS记录的ID_CUSTOMER都有对应的CUSTOMERS记录，且不存在客户重名的情况，现有SQL可以返回正确结果，但它存在两个潜在隐患，不具备通用性，不能完全匹配需求：

• 子查询SELECT MAX(AMOUNT) FROM ORDERS未过滤无关联客户的订单：如果全表最大的AMOUNT对应的订单没有匹配的客户（ID_CUSTOMER为空/不存在于CUSTOMERS表），会导致外层查询匹配不到任何数据，返回空结果，不符合「无关联客户订单不纳入统计」的要求。
• 分组逻辑存在隐患：如果存在两个不同客户的FIRSTNAME、LASTNAME完全相同，按姓名分组会将两个不同客户合并为同一条结果，丢失数据。
• 补充说明：如果同一个客户有多笔订单都达到了最大AMOUNT，现有SQL的分组逻辑会自动去重仅返回一次该客户，这个处理如果符合你“同一个客户只返回一次”的预期是没问题的，否则可以去掉GROUP BY。

二、更优实现方案 #

方案1：修正原有写法的问题 #

先在取最大值的时候就过滤掉无关联客户的订单，同时按客户ID分组避免同名问题：

SELECT c.FIRSTNAME, c.LASTNAME 
FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.ID = o.ID_CUSTOMER
WHERE o.AMOUNT = (
    SELECT MAX(o2.AMOUNT) 
    FROM ORDERS o2
    INNER JOIN CUSTOMERS c2 ON o2.ID_CUSTOMER = c2.ID
)
GROUP BY c.ID, c.FIRSTNAME, c.LASTNAME
ORDER BY c.FIRSTNAME, c.LASTNAME

方案2：用窗口函数实现（性能更优，仅需一次表关联） #

SELECT DISTINCT FIRSTNAME, LASTNAME
FROM (
    SELECT 
        c.FIRSTNAME, 
        c.LASTNAME,
        RANK() OVER(ORDER BY o.AMOUNT DESC) AS rk
    FROM CUSTOMERS c
    INNER JOIN ORDERS o ON c.ID = o.ID_CUSTOMER
) t
WHERE rk = 1
ORDER BY FIRSTNAME, LASTNAME

这个方案用RANK()窗口函数直接在关联后的结果里标记订单金额排名，避免了二次扫描ORDERS表，数据量较大的时候性能更好，同时天然过滤了无关联客户的订单，也支持多客户、多订单同属最大金额的场景。

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