Medium之1159.市场分析I

川长思鸟来 2023-01-23 08:53 99阅读 0赞

Table: Users

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>join_date</td> 
   <td>date</td> 
  </tr> 
  <tr> 
   <td>favorite_brand</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

此表主键是 user\_id，表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。

Table: 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>item_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>buyer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>seller_id</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

此表主键是 order\_id，外键是 item\_id 和（buyer\_id，seller\_id）。

Table: Item

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>item_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>item_brand</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

此表主键是 item\_id。

### 问题 ###

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

### 示例 ###

Users table:

<table> 
 <thead> 
  <tr> 
   <th>user_id</th> 
   <th>join_date</th> 
   <th>favorite_brand</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2018-01-01</td> 
   <td>Lenovo</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2018-02-09</td> 
   <td>Samsung</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2018-01-19</td> 
   <td>LG</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2018-05-21</td> 
   <td>HP</td> 
  </tr> 
 </tbody> 
</table>

Orders table:

<table> 
 <thead> 
  <tr> 
   <th>order_id</th> 
   <th>order_date</th> 
   <th>item_id</th> 
   <th>buyer_id</th> 
   <th>seller_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2019-08-01</td> 
   <td>4</td> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2018-08-02</td> 
   <td>2</td> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2019-08-03</td> 
   <td>3</td> 
   <td>2</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2018-08-04</td> 
   <td>1</td> 
   <td>4</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>2018-08-04</td> 
   <td>1</td> 
   <td>3</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>2019-08-05</td> 
   <td>2</td> 
   <td>2</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

Items table:

<table> 
 <thead> 
  <tr> 
   <th>item_id</th> 
   <th>item_brand</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Samsung</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Lenovo</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>LG</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>HP</td> 
  </tr> 
 </tbody> 
</table>

Result table:

<table> 
 <thead> 
  <tr> 
   <th>buyer_id</th> 
   <th>join_date</th> 
   <th>orders_in_2019</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2018-01-01</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2018-02-09</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2018-01-19</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2018-05-21</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

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

### 解答 ###

select user_id as buyer_id,join_date,count(buyer_id) as orders_in_2019
    from users u
         left join
         orders o on u.user_id = o.buyer_id and year(order_date)='2019'
    group by u.user_id

##### 注意 #####

外连接时要注意where和on的区别：  
1.on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容；  
2.where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。  
如果这里用的是 where year(order\_date)=‘2019’ 那么得到的结果将会把不满足条件的user\_id为3，4的行给删掉。用on的话会保留user\_id为3，4的行。