一、题目
一张orders表
有order_number(订单编号)、
customer_number（客户编号）
两个字段
二、题目要求
找出订单最多的客户编号，就是找表里面customer_number条数最多的那个
三、示例
customer_number为1的客户有1笔订单
customer_number为2的客户有1笔订单
customer_number为3的客户有2笔订单
所以customer_number等于3的客户拥有的订单笔数最多，输出结果是3
看输入：
四、分析
1、通过group by分组求出每个customer_number拥有的订单总数
select
t.customer_number,
count(1) as num
from orders t
group by t.customer_number
2、我想到的方法是先找出比去其他客户订单数量少的customer_number
然后再排掉这些customer_number，剩下的就是需要的订单笔数最多的customer_number
3、用1中的sql分别作为两个临时表，用inner join关联，可以看一下笛卡尔积后的效果
从上面的图片中
我们只要找出
①tmp1的customer_number跟tmp2的customer_number比（两者的customer_number不相等的前提下）
②tmp1的count比tmp2的count小
满足上面两个条件其实就是找出了标绿的那两条数据 我们只要tmp1表的customer_number(即找出了1，2)
4、外面再套一层，从orders表查，限制customer_number not in 第三步查出来的customer_number，取customer_number的时候去一下重，因为多订单的客户编号会有多条
五、sql实现
with
tmp1 --临时表1
as(
select
t.customer_number,
count(1) as num
from orders t
group by t.customer_number),
tmp2 --临时表2
as(
select
t.customer_number,
count(1) as num
from orders t
group by t.customer_number)
select
distinct customer_number
from orders
where customer_number not in
--外层限制客户编号不在小的订单客户编号里面
(
--内层查出订单数比别的客户少的customer_number
select
distinct tmp1.customer_number
from
tmp1 inner join tmp2
on tmp1.customer_number <> tmp2.customer_number
and tmp1.num < tmp2.num
)
还有另外一种思路，就是先找出最大的订单数，然后再去找哪些customer_number的订单数是等于最大订单数的，大家可尝试一下～