Medium之1098.小众书籍

た入场券 2023-01-23 03:58 129阅读 0赞

##### 书籍表 Books： #####

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>book_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>available_from</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

book\_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>book_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>quantity</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>dispatch_date</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

order\_id 是这个表的主键。  
book\_id 是 Books 表的外键。

### 问题 ###

你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。

注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。

### 示例 ###

Books 表：

<table> 
 <thead> 
  <tr> 
   <th>book_id</th> 
   <th>name</th> 
   <th>available_from</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>“Kalila And Demna”</td> 
   <td>2010-01-01</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>“28 Letters”</td> 
   <td>2012-05-12</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>“The Hobbit”</td> 
   <td>2019-06-10</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>“13 Reasons Why”</td> 
   <td>2019-06-01</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>“The Hunger Games”</td> 
   <td>2008-09-21</td> 
  </tr> 
 </tbody> 
</table>

Orders 表：

<table> 
 <thead> 
  <tr> 
   <th>order_id</th> 
   <th>book_id</th> 
   <th>quantity</th> 
   <th>dispatch_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>2</td> 
   <td>2018-07-26</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>1</td> 
   <td>2018-11-05</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>8</td> 
   <td>2019-06-11</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>6</td> 
   <td>2019-06-05</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>4</td> 
   <td>5</td> 
   <td>2019-06-20</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>5</td> 
   <td>9</td> 
   <td>2009-02-02</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>5</td> 
   <td>8</td> 
   <td>2010-04-13</td> 
  </tr> 
 </tbody> 
</table>

Result 表：

<table> 
 <thead> 
  <tr> 
   <th>book_id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>“Kalila And Demna”</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>“28 Letters”</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>“The Hunger Games”</td> 
  </tr> 
 </tbody> 
</table>

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

### 解答 ###

select b.book_id,b.name
    from books b
         left join
         orders o on b.book_id = o.book_id and datediff('2019-06-23',dispatch_date)<=365
    where datediff('2019-06-23',available_from)>=30
    group by b.book_id
    having ifnull(sum(quantity),0)<10

###### 易错点： ######

（1）容易忽略销量为0的书籍(用books表left join解决）  
（2）使用左链接，但是限制"近一年“使用了where字段，导致销量为null依然被忽略（所以过去一年放在on后面的条件）  
（3）having条件忘记is null，导致销量为null被忽略

注意：因为on去筛选，没有订单的至少返回null；而不符合where条件的，连null都不会保留。所以不满一个月应放在where后，过去一年中应放在on后。