网站后期维护管理,临海市住房与城乡建设规划局网站,手机网站建设价格表,网站建设营业执照如何写相信很多学习SQL的小伙伴都面临这样的困境#xff0c;学习完书本上的SQL基础知识后#xff0c;一方面想测试下自己的水平#xff1b;另一方面想进一步提升#xff0c;却不知道方法。
其实#xff0c;对于技能型知识#xff0c;我的观点一贯都是#xff1a;多练习、多实…相信很多学习SQL的小伙伴都面临这样的困境学习完书本上的SQL基础知识后一方面想测试下自己的水平另一方面想进一步提升却不知道方法。
其实对于技能型知识我的观点一贯都是多练习、多实践。正所谓实践出真知学完书本的知识很多时候也只能做到知道距离熟练的应用还差的很远。
在咱们程序员圈子里力扣LeetCode和牛客nowcoder.com是两个公认比较好的实践平台。题库比较多还有不少大厂的笔试真题特别适合找工作时撸一撸。当然作为平时个人技术提升的练习题也是非常不错的。
最近一段时间我会先从力扣LeetCode的SQL题刷起。当然顺序可能是随机的欢迎小伙伴们点题。
题目1549. 每件商品的最新订单
通过次数5,893 | 提交次数8,629通过率68.29%
表: Customers
------------------------
| Column Name | Type |
------------------------
| customer_id | int |
| name | varchar |
------------------------
customer_id 是该表主键.
该表包含消费者的信息.表: Orders
------------------------
| Column Name | Type |
------------------------
| order_id | int |
| order_date | date |
| customer_id | int |
| product_id | int |
------------------------
order_id 是该表主键.
该表包含消费者customer_id产生的订单.
不会有商品被相同的用户在一天内下单超过一次.表: Products
------------------------
| Column Name | Type |
------------------------
| product_id | int |
| product_name | varchar |
| price | int |
------------------------
product_id 是该表主键.
该表包含所有商品的信息.写一个SQL 语句, 找到每件商品的最新订单(可能有多个).
返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.
查询结果格式如下例所示。示例 1输入:
Customers表
------------------------
| customer_id | name |
------------------------
| 1 | Winston |
| 2 | Jonathan |
| 3 | Annabelle |
| 4 | Marwan |
| 5 | Khaled |
------------------------
Orders表
-----------------------------------------------
| order_id | order_date | customer_id | product_id |
-----------------------------------------------
| 1 | 2020-07-31 | 1 | 1 |
| 2 | 2020-07-30 | 2 | 2 |
| 3 | 2020-08-29 | 3 | 3 |
| 4 | 2020-07-29 | 4 | 1 |
| 5 | 2020-06-10 | 1 | 2 |
| 6 | 2020-08-01 | 2 | 1 |
| 7 | 2020-08-01 | 3 | 1 |
| 8 | 2020-08-03 | 1 | 2 |
| 9 | 2020-08-07 | 2 | 3 |
| 10 | 2020-07-15 | 1 | 2 |
-----------------------------------------------
Products表
---------------------------------
| product_id | product_name | price |
---------------------------------
| 1 | keyboard | 120 |
| 2 | mouse | 80 |
| 3 | screen | 600 |
| 4 | hard disk | 450 |
---------------------------------
输出
------------------------------------------------
| product_name | product_id | order_id | order_date |
------------------------------------------------
| keyboard | 1 | 6 | 2020-08-01 |
| keyboard | 1 | 7 | 2020-08-01 |
| mouse | 2 | 8 | 2020-08-03 |
| screen | 3 | 3 | 2020-08-29 |
------------------------------------------------
解释
keyboard 的最新订单在2020-08-01, 在这天有两次下单.
mouse 的最新订单在2020-08-03, 在这天只有一次下单.
screen 的最新订单在2020-08-29, 在这天只有一次下单.
hard disk 没有被下单, 我们不把它包含在结果表中.来源力扣LeetCode
链接https://leetcode.cn/problems/the-most-recent-orders-for-each-product #测试数据
Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists Products (product_id int, product_name varchar(20), price int);insert into Customers (customer_id, name) values (1, Winston);
insert into Customers (customer_id, name) values (2, Jonathan);
insert into Customers (customer_id, name) values (3, Annabelle);
insert into Customers (customer_id, name) values (4, Marwan);
insert into Customers (customer_id, name) values (5, Khaled);insert into Orders (order_id, order_date, customer_id, product_id) values (1, 2020-07-31, 1, 1);
insert into Orders (order_id, order_date, customer_id, product_id) values (2, 2020-7-30, 2, 2);
insert into Orders (order_id, order_date, customer_id, product_id) values (3, 2020-08-29, 3, 3);
insert into Orders (order_id, order_date, customer_id, product_id) values (4, 2020-07-29, 4, 1);
insert into Orders (order_id, order_date, customer_id, product_id) values (5, 2020-06-10, 1, 2);
insert into Orders (order_id, order_date, customer_id, product_id) values (6, 2020-08-01, 2, 1);
insert into Orders (order_id, order_date, customer_id, product_id) values (7, 2020-08-01, 3, 1);
insert into Orders (order_id, order_date, customer_id, product_id) values (8, 2020-08-03, 1, 2);
insert into Orders (order_id, order_date, customer_id, product_id) values (9, 2020-08-07, 2, 3);
insert into Orders (order_id, order_date, customer_id, product_id) values (10, 2020-07-15, 1, 2);insert into Products (product_id, product_name, price) values (1, keyboard, 120);
insert into Products (product_id, product_name, price) values (2, mouse, 80);
insert into Products (product_id, product_name, price) values (3, screen, 600);
insert into Products (product_id, product_name, price) values (4, hard disk, 450);
解题思路
跟8月23日的推文《1077. 项目员工 III》一样这道题考查的也是分组内排名然后返回前N名的写法。
但是也有2点特殊要求
第一如果最新的一天有多条记录那么都需要返回
第二对于返回的结果需要按要求排序返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列
那么在实现上也主要是分为以下3个步骤
第一步先计算出每个产品每个订单日期的排名倒序如果最新的日期内有多笔订单则需要全部返回。
针对这类分组内排序后取前N名的需求有三个分析函数可以使用分别是row_number、rank、dense_rank。
根据需求这里比较适合使用的是rank和dense_rank然后限定排序序号为1即可。
第二步关联出返回结果中需要的product_name。可以使用product_id与Products关联获取product_name的值。
第三步使用order by子句对返回的结果集进行排序。
参考SQL selectc.product_name,b.product_id,b.order_id,b.order_date
from (selecta.product_id,a.order_id,a.order_date,rank() over(partition by a.product_id order by order_date desc) rkfrom Orders a
)b
left join Products c
on b.product_id c.product_id
where b.rk 1
order by c.product_name,b.product_id,b.order_id;
