校招算法笔面试 | SQL笔试面试编程题-返回顾客名称和相关订单号以及每个订单的总价

题目## 题目

题目链接

最近做了京东的24年春招题，题目如下：

题目分析

在这道题目中，我们需要从三个表中提取数据：Customers、Orders 和 OrderItems。以下是每个表的结构及字段的汉语意思：

1. Customers（客户表）

   • cust_id：客户ID
   • cust_name：客户名称

2. Orders（订单表）

   • order_num：订单编号
   • cust_id：客户ID

3. OrderItems（订单项表）

   • order_num：订单编号
   • item_price：商品价格
   • quantity：商品数量

目标：计算每个客户的每个订单的总金额。

输出要求：输出每个客户的名称、订单编号以及该订单的总金额，按客户名称和订单编号排序。

知识点关键词：SQL连接、聚合函数、分组、排序

解答步骤

1. 连接表

   我们需要将三个表连接在一起，以便获取每个订单的详细信息。使用 JOIN 语句连接 Customers 和 Orders 表，通过 cust_id 字段连接，然后再通过 order_num 字段连接 Orders 和 OrderItems 表。

   from Customers c
   join Orders o on o.cust_id = c.cust_id
   join OrderItems os on os.order_num = o.order_num
   

2. 计算订单总金额

   使用 SUM 函数计算每个订单的总金额。总金额是通过将每个订单项的价格乘以数量，然后对所有订单项求和得到的。

   sum(os.item_price * os.quantity) as OrderTotal
   

3. 分组和排序

   使用 GROUP BY 语句按客户名称和订单编号分组，以便计算每个订单的总金额。然后使用 ORDER BY 语句按客户名称和订单编号排序输出结果。

   group by c.cust_name, o.order_num
   order by cust_name, order_num
   

完整代码

select c.cust_name, o.order_num,
sum(os.item_price * os.quantity) as OrderTotal
from Customers c
join Orders o on o.cust_id = c.cust_id
join OrderItems os on os.order_num = o.order_num
group by c.cust_name, o.order_num
order by cust_name, order_num

近似题目练习推荐

获取指定客户每月的消费额

• 知识点：SQL连接、聚合函数、分组

统计所有课程参加培训人次

• 知识点：SQL连接、聚合函数、分组、排序

题目链接

最近做了京东的24年春招题，题目如下：

题目分析

在这道题目中，我们需要从 play_record_tb 表中找出每个 cid 的最大峰值用户数（peak_uv），并按降序排列，最后输出前三个 cid。表结构如下：

• cid：内容ID
• start_time：播放开始时间
• end_time：播放结束时间

目标：找出每个 cid 的最大峰值用户数。

输出要求：输出 cid 和对应的最大峰值用户数，按降序排列，取前3个。

知识点关键词：自连接、条件聚合、分组、排序、限制输出

解答步骤

1. 自连接计算重叠用户数

   首先，我们需要通过自连接来计算每个 cid 在任意时间段内的重叠用户数。通过自连接 play_record_tb 表，使用 p1 和 p2 两个别名，检查 p1.start_time 是否在 p2.start_time 和 p2.end_time 之间。

   表的连接及分组：

from play_record_tb p1
join play_record_tb p2 on p1.cid = p2.cid
group by p1.cid, p1.id

• 代码解释：将表格play_record_tb自连接，来确定每一个视频id是否曾同时播放过，此时注意分组要同时包含cid和id，否则在后续sum计算中把所有视频id的数量都累加一遍，但我们只需要取最大值而不是总和。

round(sum(if(p1.start_time between p2.start_time and p2.end_time, 1, 0)), 3) as peak_uv

• 代码解释：这里使用 if 函数来判断 p1.start_time 是否在 p2 的时间范围内，如果是则计数为1，否则为0。round 函数用于将结果四舍五入到小数点后三位。

2. 计算每个 cid 的最大峰值用户数

   在上一步的基础上，我们需要对每个 cid 计算出最大峰值用户数。

   select cid,
          max(peak_uv) as max_peak_uv
   from (
       -- 上一步的查询结果
   ) p
   group by cid
   

   • 代码解释：使用子查询将上一步的结果作为临时表 p，然后对 cid 进行分组，使用 max 函数找出每个 cid 的最大 peak_uv。

3. 排序并限制输出

   最后，我们需要对结果按 max_peak_uv 降序排列，并限制输出前三个结果。

   order by max_peak_uv desc
   limit 3
   

   • 代码解释：order by 用于对结果进行排序，desc 表示降序排列，limit 3 限制输出前三个结果。

完整代码

select cid,
       max(peak_uv) as max_peak_uv
from (
    select p1.cid,
           round(sum(if(p1.start_time between p2.start_time and p2.end_time, 1, 0)), 3) as peak_uv
    from play_record_tb p1
    join play_record_tb p2 on p1.cid = p2.cid
    group by p1.cid, p1.id
) p
group by cid
order by max_peak_uv desc
limit 3

近似题目练习推荐

获取员工其当前的薪水比其manager当前薪水还高的相关信息

• 知识点：自连接、子查询、条件过滤

异常的邮件概率

• 知识点：聚合函数、分组求和、条件过滤、子查询

牛客每个人最近的登录日期(三)

• 知识点：自连接、窗口函数、分组、排序