订单表(Order)中包含的订单明细数据如下图,其中OrderId表示订单编号,CustomerId表示客户编号,OrderDate表示下订单的日期,现需要编写一个查询语句,查询订单表(Order)中订单最多的客户编号和订单量。
方法一:在having中使用子查询
第一步,对客户编号(CustomerId)进行分组计数,计算出各个客户的订单量。
select CustomerId,count(*) as OrderNumber
from Orders
group by CustomerId;
第二步,找出最大的订单量
select max(OrderNumber)
from(
select CustomerId,count(*) as OrderNumber
from Orders o1
group by o1.CustomerId
)o;
第三步,用各个客户的订单量与最大订单量比较,找出订单最多的客户编号和订单量。
select o2.CustomerId,count(*) as OQ
from Orders o2
group by o2.CustomerId
having count(*)=(
select max(OrderNumber)
from(
select CustomerId,count(*) as OrderNumber
from Orders o1
group by o1.CustomerId
)o
);
方法二:使用窗口函rank()(此方法mysql8.0及以上版本可用)
第一步,对客户编号(CustomerId)进行分组计数,计算出各个客户的订单量。
select CustomerId,count(*) as OrderNumber
from Orders
group by CustomerId;
第二步:使用窗口函数rank()对订单量进行排名。
select CustomerId,OrderNumber,rank()over(order by OrderNumber desc)Rnk
from(
select CustomerId,count(*) as OrderNumber
from Orders
group by CustomerId
)o;
第三步,筛选排名第一的数据即为订单最多的客户编号和订单量。
select CustomerId,OrderNumber as OQ
from(
select CustomerId,OrderNumber ,rank()over(order by OrderNumber desc)Rnk
from(
select CustomerId,count(*) as OrderNumber
from Orders
group by CustomerId
)o
)o2
where Rnk=1;
方法三:使用自定义变量
方法二中第二步对订单量进行排名也可使用自定义变量的方式实现,因此最终代码也可编写为:
select CustomerId,OrderNumber as OQ
from(
select CustomerId,OrderNumber ,@a:=if(@pre=OrderNumber,@a,@a+1)Rnk,@pre:=OrderNumber
from(
select CustomerId,count(*) as OrderNumber
from Orders
group by CustomerId
)o,(select @a:=0,@pre:=null)p
order by OrderNumber desc
)o2
where Rnk=1
order by CustomerId;
您看此文用 · 秒,转发只需1秒呦~
既然在看了,就点一下吧!!