1. 题目描述
表: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
+----------------+---------+
user_id 是此表主键(具有唯一值的列)
表中描述了购物网站的用户信息,用户可以在此网站上进行商品买卖
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
+---------------+---------+
order_id 是此表主键(具有唯一值的列)
item_id 是 Items 表的外键(reference 列)
(buyer_id, seller_id)是 User 表的外键
表: Items
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| item_id | int |
| item_brand | varchar |
+---------------+---------+
item_id 是此表的主键(具有唯一值的列)
编写解决方案找出每个用户的注册日期和在 2019 年作为买家的订单总数
以任意顺序返回结果表
2. 测试用例
输入:
Users 表:
+---------+------------+----------------+
| user_id | join_date | favorite_brand |
+---------+------------+----------------+
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
| 4 | 2018-05-21 | HP |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2018-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2018-08-04 | 1 | 4 | 2 |
| 5 | 2018-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
+---------+------------+
输出:
+-----------+------------+----------------+
| buyer_id | join_date | orders_in_2019 |
+-----------+------------+----------------+
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
+-----------+------------+----------------+
3. 解题思路
- 将
Users表左外连接Orders表, 关联条件u.user_id = o.buyer_id
select u.user_id as buyer_id,
u.join_date as join_date,
o.order_id,
o.order_date
from Users as u
left join Orders as o on u.user_id = o.buyer_id
查询结果
+--------+----------+--------+----------+
|buyer_id|join_date |order_id|order_date|
+--------+----------+--------+----------+
|1 |2018-01-01|2 |2018-08-02|
|1 |2018-01-01|1 |2019-08-01|
|2 |2018-02-09|6 |2019-08-05|
|2 |2018-02-09|3 |2019-08-03|
|3 |2018-01-19|5 |2018-08-04|
|4 |2018-05-21|4 |2018-08-04|
+--------+----------+--------+----------+
- 对 xx 进行分组统计
group by u.user_id, u.join_date, 计算每个购买者在 2019 年的订单总数sum(if(year(o.order_date) = 2019, 1, 0)) as orders_in_2019
select u.user_id as buyer_id,
u.join_date as join_date,
sum(if(year(o.order_date) = 2019, 1, 0)) as orders_in_2019
from Users as u
left join Orders as o on u.user_id = o.buyer_id
group by u.user_id, u.join_date;
查询结果
+--------+----------+--------------+
|buyer_id|join_date |orders_in_2019|
+--------+----------+--------------+
|1 |2018-01-01|1 |
|2 |2018-02-09|2 |
|3 |2018-01-19|0 |
|4 |2018-05-21|0 |
+--------+----------+--------------+
