在派生表中使用max()函数返回意外结果的SQL问题排查

问题场景 #

给定Orders表数据：

Orders表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+--------------+-----------------+

需求是找出下订单数量最多的客户的customer_number，预期输出为3，但执行以下SQL后返回结果为[2,1]：

select max(c.d),customer_number from (Select Count(order_number) as d,customer_number  from Orders group by customer_number) as c

异常原因 #

你的SQL存在分组逻辑错误：外层查询同时使用了聚合函数max(c.d)和非聚合列customer_number，但未指定GROUP BY子句。这种情况下，数据库会随机返回一个非聚合列的值（此处恰好返回了customer_number=1），无法保证该值与max(c.d)对应。

正确写法 #

方式1：排序取第一条（简单高效）

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC
LIMIT 1;

方式2：子查询匹配最大订单数

SELECT customer_number
FROM Orders
GROUP BY customer_number
HAVING COUNT(order_number) = (
    SELECT MAX(order_count)
    FROM (
        SELECT COUNT(order_number) AS order_count
        FROM Orders
        GROUP BY customer_number
    ) AS temp
);

方式3：窗口函数（支持MySQL8+、PostgreSQL等）

如果存在多个客户订单数并列最多的情况，该方式可返回所有符合条件的客户：

SELECT customer_number
FROM (
    SELECT 
        customer_number,
        RANK() OVER(ORDER BY COUNT(order_number) DESC) AS rnk
    FROM Orders
    GROUP BY customer_number
) AS temp
WHERE rnk = 1;

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