?? 1、專欄介紹

「SQL面試題庫」是由 不是西紅柿 發(fā)起，全員免費(fèi)參與的SQL學(xué)習(xí)活動(dòng)。我每天發(fā)布1道SQL面試真題，從簡單到困難，涵蓋所有SQL知識(shí)點(diǎn)，我敢保證只要做完這100道題，不僅能輕松搞定面試，代碼能力和工作效率也會(huì)有明顯提升。

1.1 活動(dòng)流程

1. 整理題目：西紅柿每天無論刮風(fēng)下雨，保證在8am 前，更新一道新鮮SQL面試真題。
2. 粉絲打卡：粉絲們可在評(píng)論區(qū)寫上解題思路，或者直接完成SQL代碼，有困難的小伙伴不要著急，先看別人是怎么解題的，邊看邊學(xué)，不懂就問我。
3. 交流討論：為了方便交流討論，可進(jìn)入 數(shù)據(jù)倉庫 。
4. 活動(dòng)獎(jiǎng)勵(lì)：我每天都會(huì)看評(píng)論區(qū)和群里的內(nèi)容，對(duì)于積極學(xué)習(xí)和熱心解答問題的小伙伴，紅包鼓勵(lì)，以營造更好的學(xué)習(xí)氛圍。

1.2 你的收獲

1. 增強(qiáng)自信，搞定面試：在求職中，SQL是經(jīng)常遇到的技能點(diǎn)，而這些題目也多數(shù)是真實(shí)的面試題，刷題可以讓我們更好地備戰(zhàn)面試，增強(qiáng)自信，提升自己的核心競爭力。

2. 鞏固SQL語法，高效搞定工作：通過不斷練習(xí)，能夠熟悉SQL的語法和常用函數(shù)，掌握SQL核心知識(shí)點(diǎn)，提高SQL編寫能力。代碼能力提升了，工作效率自然高了。

3. 提高數(shù)據(jù)處理能力、鍛煉思維能力：SQL是數(shù)據(jù)處理的核心工具，通過刷題可以讓我們更好地理解數(shù)據(jù)處理的過程，提高數(shù)據(jù)分析的效率。SQL題目的難度不一，需要在一定時(shí)間內(nèi)解決問題，培養(yǎng)了我們對(duì)問題的思考能力、解決問題的能力和對(duì)時(shí)間的把控能力等。

?? 2、今日真題

題目介紹： The Most Recent Orders for Each Product the-most-recent-orders-for-each-product

難度中等

SQL架構(gòu)

Table:

Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id is the primary key for this table.
This table contains information about the customers.

Table:

Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| product_id    | int     |
+---------------+---------+
order_id is the primary key for this table.
This table contains information about the orders made by customer_id.
There will be no product ordered by the same user more than once in one day.

Table:

Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| price         | int     |
+---------------+---------+
product_id is the primary key for this table.
This table contains information about the Products.

Write an SQL query to find the most recent order(s) of each product.

Return the result table sorted by

product_name

in ascending order and in case of a tie by the

product_id

in ascending order. If there still a tie, order them by the

order_id

in ascending order.

The query result format is in the following example:

``` 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 | +------------+--------------+-------+

Result table: +--------------+------------+----------+------------+ | 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's most recent order is in 2020-08-01, it was ordered two times this day. mouse's most recent order is in 2020-08-03, it was ordered only once this day. screen's most recent order is in 2020-08-29, it was ordered only once this day. The hard disk was never ordered and we don't include it in the result table. ```文章來源地址http://www.zghlxwxcb.cn/news/detail-554149.html

sql
select product_name,product_id,order_id,order_date
from
(
select product_name ,o.product_id ,order_id,order_date ,
    rank() over(partition by o.product_id order by order_date desc) rk
from Orders o left join Products p
on o.product_id =p.product_id 
)t1
where rk =1
order by product_name,product_id,order_id

• 已經(jīng)有靈感了？在評(píng)論區(qū)寫下你的思路吧！