586. 订单最多的客户

Posted on | In
Words count in article: 250 | Reading time ≈ 1

题目

在表 orders 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有一位。

表 orders 定义如下：

1
2
3
4
5
6
7
8
9
| Column            | Type      |
|-------------------|-----------|
| order_number (PK) | int       |
| customer_number   | int       |
| order_date        | date      |
| required_date     | date      |
| shipped_date      | date      |
| status            | char(15)  |
| comment           | char(200) |

示例1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1            | 1               | 2017-04-09 | 2017-04-13    | 2017-04-12   | Closed |         |
| 2            | 2               | 2017-04-15 | 2017-04-20    | 2017-04-18   | Closed |         |
| 3            | 3               | 2017-04-16 | 2017-04-25    | 2017-04-20   | Closed |         |
| 4            | 3               | 2017-04-18 | 2017-04-28    | 2017-04-25   | Closed |         |

输出

| customer_number |
|-----------------|
| 3               |

customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单
所以结果是该顾客的 customer_number ，也就是 3 。

进阶:

如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

解法

解法一：

SQL

1
2
3
4
5
6
7
SELECT
	customer_number
FROM
	orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

解法二：

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Write your MySQL query statement below
SELECT
	customer_number
FROM
	orders o1
GROUP BY o1.customer_number
HAVING COUNT(*) = (
			SELECT
				COUNT(*)
			FROM
				orders o2
			GROUP BY o2. customer_number
			ORDER BY COUNT(*) DESC
			LIMIT 1
	);
0%