- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
Orders
+-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | customer_number | int | +-----------------+----------+ 在 SQL 中,Order_number是该表的主键。 此表包含关于订单ID和客户ID的信息。查找下了 最多订单 的客户的
customer_number
。测试用例生成后, 恰好有一个客户 比任何其他客户下了更多的订单。
查询结果格式如下所示。
示例 1:
输入: Orders 表: +--------------+-----------------+ | order_number | customer_number | +--------------+-----------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 3 | +--------------+-----------------+ 输出: +-----------------+ | customer_number | +-----------------+ | 3 | +-----------------+ 解释: customer_number 为 '3' 的顾客有两个订单,比顾客 '1' 或者 '2' 都要多,因为他们只有一个订单。 所以结果是该顾客的 customer_number ,也就是 3 。进阶: 如果有多位顾客订单数并列最多,你能找到他们所有的
customer_number
吗?
三,建表语句
drop database db_likou;
create database db_likou;
use db_likou;
Create table If Not Exists orders (order_number int, customer_number int);
Truncate table orders;
insert into orders (order_number, customer_number) values ('1', '1');
insert into orders (order_number, customer_number) values ('2', '2');
insert into orders (order_number, customer_number) values ('3', '3');
insert into orders (order_number, customer_number) values ('4', '3');
insert into orders (order_number, customer_number) values ('5', '4');
insert into orders (order_number, customer_number) values ('6', '4');
四,分析
题解:
表:订单表
字段:订单id,客户id
求: 如果有多位顾客订单数并列最多,你能找到他们所有的
customer_number
吗?
思路
找到客户订单最多 客户
第一步,以客户分组,count订单数量
第二步,找到最大的客户订单数量 注意 可能有多个用户的订单是一样的,所以继续开窗
max 全部 不分组
第三步,用户的count次数=最大的次数 拿到顾客的id
五,SQL解答
with t1 as (
select order_number,customer_number,
count(order_number) over(partition by customer_number) rn
from orders
),t2 as (
select order_number,customer_number,t1.rn,
max(t1.rn) over() mo
from t1
)
select distinct customer_number from t2 where rn =mo;
六,验证
七,知识点总结
- 分组聚合的联系;
- count 开窗的联系;
- max开窗的联系
- where条件判断的练习
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用