Medium之1532.最近的三笔订单***

逃离我推掉我的手 2022-08-31 11:26 131阅读 0赞

**表：Customers**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>customer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

customer\_id 是该表主键  
该表包含消费者的信息

**表：Orders**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>order_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>order_date</td> 
   <td>date</td> 
  </tr> 
  <tr> 
   <td>customer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>cost</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

order\_id 是该表主键  
该表包含id为customer\_id的消费者的订单信息  
每一个消费者 每天一笔订单

### 问题 ###

写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。

返回的结果按照 customer\_name 升序排列。如果排名有相同，则继续按照 customer\_id 升序排列。如果排名还有相同，则继续按照 order\_date 降序排列。

### 示例 ###

**Customers**

<table> 
 <thead> 
  <tr> 
   <th>customer_id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Winston</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jonathan</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Annabelle</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Marwan</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Khaled</td> 
  </tr> 
 </tbody> 
</table>

**Orders**

<table> 
 <thead> 
  <tr> 
   <th>order_id</th> 
   <th>order_date</th> 
   <th>customer_id</th> 
   <th>cost</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2020-07-31</td> 
   <td>1</td> 
   <td>30</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2020-07-30</td> 
   <td>2</td> 
   <td>40</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2020-07-31</td> 
   <td>3</td> 
   <td>70</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2020-07-29</td> 
   <td>4</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>2020-06-10</td> 
   <td>1</td> 
   <td>1010</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>2020-08-01</td> 
   <td>2</td> 
   <td>102</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-08-01</td> 
   <td>3</td> 
   <td>111</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>2020-08-03</td> 
   <td>1</td> 
   <td>99</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>2020-08-07</td> 
   <td>2</td> 
   <td>32</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>2020-07-15</td> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

**Result table：**

<table> 
 <thead> 
  <tr> 
   <th>customer_name</th> 
   <th>customer_id</th> 
   <th>order_id</th> 
   <th>order_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Annabelle</td> 
   <td>3</td> 
   <td>7</td> 
   <td>2020-08-01</td> 
  </tr> 
  <tr> 
   <td>Annabelle</td> 
   <td>3</td> 
   <td>3</td> 
   <td>2020-07-31</td> 
  </tr> 
  <tr> 
   <td>Jonathan</td> 
   <td>2</td> 
   <td>9</td> 
   <td>2020-08-07</td> 
  </tr> 
  <tr> 
   <td>Jonathan</td> 
   <td>2</td> 
   <td>6</td> 
   <td>2020-08-01</td> 
  </tr> 
  <tr> 
   <td>Jonathan</td> 
   <td>2</td> 
   <td>2</td> 
   <td>2020-07-30</td> 
  </tr> 
  <tr> 
   <td>Marwan</td> 
   <td>4</td> 
   <td>4</td> 
   <td>2020-07-29</td> 
  </tr> 
  <tr> 
   <td>Winston</td> 
   <td>1</td> 
   <td>8</td> 
   <td>2020-08-03</td> 
  </tr> 
  <tr> 
   <td>Winston</td> 
   <td>1</td> 
   <td>1</td> 
   <td>2020-07-31</td> 
  </tr> 
  <tr> 
   <td>Winston</td> 
   <td>1</td> 
   <td>10</td> 
   <td>2020-07-15</td> 
  </tr> 
 </tbody> 
</table>

Winston 有 4 笔订单, 排除了 “2020-06-10” 的订单, 因为它是最老的订单。  
Annabelle 只有 2 笔订单, 全部返回。  
Jonathan 恰好有 3 笔订单。  
Marwan 只有 1 笔订单。  
结果表我们按照 customer\_name 升序排列，customer\_id 升序排列，order\_date 降序排列。

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/the-most-recent-three-orders  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：窗口函数 #####

select name as customer_name,t.customer_id,order_id,order_date
    from (
        select order_id,order_date,customer_id,
        row_number() over(partition by customer_id order by order_date desc) rw 
        from orders) t
        join customers c on t.customer_id=c.customer_id
    where rw<=3
    order by name,customer_id,order_date desc

注意：窗口函数里面over(partition by 后面一定要跟上order by order\_date desc)

##### 法二：内连接——没懂 #####

select name as customer_name,o1.customer_id,o1.order_id,o1.order_date
    from orders o1,orders o2,customers c 
    where o1.customer_id=o2.customer_id and o1.order_date<=o2.order_date and o1.customer_id=c.customer_id
    group by o1.order_id
    having count(o2.order_date)<=3
    order by name,customer_id,order_date desc