8/7 每日一題(MYSQL 找出訂單數最多的客戶)

 

Table: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key (column with unique values) for this table.
This table contains information about the order ID and the customer ID.

 

Write a solution to find the customer_number for the customer who has placed the largest number of orders.

The test cases are generated so that exactly one customer will have placed more orders than any other customer.

The result format is in the following example.

 

Example 1:

Input: 
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
Explanation: 
The customer with number 3 has two orders, which is greater than either customer 1 or 2 because each of them only has one order. 
So the result is customer_number 3.

 

Follow up: What if more than one customer has the largest number of orders, can you find all the customer_number in this case?

# Write your MySQL query statement below

SELECT customer_number    

#輸出customer_number 欄位

FROM Orders

GROUP BY customer_number

#用customer_number欄位分群

HAVING COUNT(customer_number)

#計算該群組的數量

ORDER BY COUNT(customer_number) DESC

#分組數量的降序

LIMIT 1;

#只回傳第一個也就是最大的

---------------------------參考解答

select customer_number 
from Orders
group by customer_number 
order by count(customer_number) desc
limit 1;