SQL查找每个城市购买金额排名第二的用户，列出其购买城市、姓名、购买金额（排序函数）

Begin to change

于 2022-08-11 09:16:52 发布

阅读量1.5k

点赞数

分类专栏： # SQL面试题文章标签： sql 数据库

本文链接： https://blog.csdn.net/qq_41404557/article/details/126268626

版权

SQL面试题专栏收录该内容

69 篇文章 15 订阅

订阅专栏

【面试题】某公司数据库里有3张表，销售订单表、产品明细表、销售网点表

”销售订单表”记录了销售情况，每一张数据表示哪位顾客、在哪一天、哪个网点购买了什么产品，购买的数量是多少，以及对应产品的零售价

销售订单表、产品明细表、销售网点表字段之间的关系如下

销售订单表和产品明细表通过“产品”字段关联，销售订单表和销售网点通过“交易网点”关联

【问题】查找每个城市购买金额排名第二的用户，列出其购买城市、姓名、购买金额

【解题思路】

此题逻辑上有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;