1 题目描述
表: Orders
+-----------------+----------+
| Column Name | Type |
+-----------------+----------+
| order_number | int |
| customer_number | int |
+-----------------+----------+
在 SQL 中, Order_number 是该表的主键
此表包含关于订单 ID 和客户 ID 的信息
查找下了最多订单 的客户的 customer_number
测试用例生成后, 恰好有一个客户 比任何其他客户下了更多的订单
2 测试用例
输入:
Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+--------------+-----------------+
输出:
+-----------------+
| customer_number |
+-----------------+
| 3 |
+-----------------+
解释:
customer_number 为'3' 的顾客有两个订单, 比顾客'1' 或者'2' 都要多, 因为他们只有一个订单
所以结果是该顾客的 customer_number, 也就是 3
3 解题思路
3.1 解法 1: group by + order by + count()
- 对
customer_number分组统计数量, 并按照统计数量倒序排序
select customer_number, count(*) as customer_number_count
from Orders
group by customer_number
order by count(*) desc
查询结果
+---------------+---------------------+
|customer_number|customer_number_count|
+---------------+---------------------+
|3 |2 |
|1 |1 |
|2 |1 |
+---------------+---------------------+
- 使用 limit 返回第一条数据
select customer_number, count(*) as customer_number_count
from Orders
group by customer_number
order by count(*) desc
limit 0,1;
查询结果
+---------------+---------------------+
|customer_number|customer_number_count|
+---------------+---------------------+
|3 |2 |
+---------------+---------------------+
- 去掉步骤 2 中的
customer_number_count, 最终满足题目要求的 sql
select customer_number
from Orders
group by customer_number
order by count(*) desc
limit 0,1;
查询结果
+---------------+
|customer_number|
+---------------+
|3 |
+---------------+
3.2 解法 2: order by + count() over()
- 使用
count(*) over (partition by customer_number)分组统计数量, 并对数量进行倒序排序
select distinct customer_number, count(*) over (partition by customer_number) as customer_number_count
from Orders
order by count(*) over (partition by customer_number) desc
查询结果
+---------------+---------------------+
|customer_number|customer_number_count|
+---------------+---------------------+
|3 |2 |
|1 |1 |
|2 |1 |
+---------------+---------------------+
- 使用
limit返回第一条数据
select distinct customer_number, count(*) over (partition by customer_number) as customer_number_count
from Orders
order by count(*) over (partition by customer_number) desc
limit 0,1;
查询结果
+---------------+---------------------+
|customer_number|customer_number_count|
+---------------+---------------------+
|3 |2 |
+---------------+---------------------+
- 去掉步骤 2 中的
customer_number_count, 最终满足题目要求的 sql
select distinct customer_number
from Orders
order by count(*) over (partition by customer_number) desc
limit 0,1;
查询结果
+---------------+
|customer_number|
+---------------+
|3 |
+---------------+
