遇到排名问题，怎么办？
”销售订单表”记录了销售情况，每一张数据表示哪位顾客、在哪一天、哪个网点购买了什么产品，购买的数量是多少，以及对应产品的零售价
“产品明细表”记录了公司产品的详细信息
“销售网点表”记录了公司的销售网点
销售订单表、产品明细表、销售网点表字段之间的关系如下
销售订单表和产品明细表通过“产品”字段关联，销售订单表和销售网点通过“交易网点”关联
【问题】查找每个城市购买金额排名第二的用户，列出其购买城市、姓名、购买金额
【解题思路】
此题逻辑上有3步：
1）第一步每个城市每个用户总的购买金额
2）第二步找出购买金额第二的用户
3）第三步分组列出购买城市、姓名、购买金额
1.每个城市每个用户总的购买金额
需要“销售网点表”、”销售订单表”进行多表联结
select
c.城市,
a.顾客ID,
sum(a.销售数量*a.零售价格) as 购买金额
from 销售订单表 as a
inner join 销售网点表 as c
on a.交易网点=c.交易网点
group by c.城市,a.顾客ID;2.找出购买金额第二的用户
把上一步的查询结果记录为临时表t1。这个是典型的排名问题，要想到《猴子 从零学会SQL》里讲过的用窗口函数来实现
因有可能销售金额的是一样的，避免漏下，因此用窗口函数dense_rank对购买金额进行排序
select
t1.城市,
t1.顾客ID,
t1.购买金额,
dense_rank() over (partition by t1.城市
order by t1.购买金额 desc) as 排名
from t1;3.找出排名第二名的用户
上面排名后，就可以用where筛选出排名第二名的用户
select
t1.城市,
t1.顾客ID,
t1.购买金额,
dense_rank() over (partition by t1.城市
order by t1.购买金额 desc) as 排名
from t1
where 排名=2;我们把第1步得到的t1表示的SQL带入上面，就得到了最终SQL
select
t1.城市,
t1.顾客ID,
t1.购买金额,
dense_rank() over (partition by t1.城市
order by t1.购买金额 desc) as 排名
from (
select
c.城市,
a.顾客ID,
sum(a.销售数量*a.零售价格) as 购买金额
from 销售订单表 as a
inner join 销售网点表 as c
on a.交易网点=c.交易网点
group by c.城市,a.顾客ID
) as t1
where 排名=2;查询结果：
【本题考点】
1.排名问题，要想到使用窗口函数来进行排序。
2.排序函数有3个比较常用的，需要区分各个函数的输出结果：
row_number(): 返回结果为连续的排序：1,2,3,4,5...
rank(): 返回结果为跳跃排序，也就是为相同数值的行输出相同排序结果，对于下一行不同的数据将返回行号：1,1,3,4...
dense_rank(): 返回结果为连续排名，排名值没有间断：1,1,2,3,4...
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-01-20，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读