在派生表中使用max()返回了意外结果

Question

编写一个SQL查询来查找下了最多订单的客户的customer_number。

Input: 
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+

我的SQL:

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

预期结果：

[2,3]

实际结果：

[2,1]

Answer 1

您可以在此处使用LIMIT查询：

SELECT customer_number, COUNT(*) AS num_orders
FROM Orders
GROUP BY customer_number
ORDER BY num_orders DESC
LIMIT 1;

假设您可以为具有相同最大订单数的两个或多个客户建立联系，那么在MySQL 8+上使用RANK将是最简单的方法：

WITH cte AS (
SELECT customer_number, COUNT(*) AS num_orders,
RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM Orders
GROUP BY customer_number
)
SELECT customer_number, num_orders
FROM cte
WHERE rnk = 1;