Leetcode - SQL 1158 -Case When使用

题目

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

The query result format is in the following example:

Users table:
±--------±-----------±---------------+
| 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 table:

| 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 table:
±--------±-----------+
| item_id | item_brand |
±--------±-----------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
±--------±-----------+

Result table:
±----------±-----------±---------------+
| 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 |
±----------±-----------±---------------+

思路

如果我们使用常规的 where year(order_date) = ‘2019’ 语法来筛选2019年数据的话，那么就只会的出1和2的数据，不会得出2019年没有订单的数据，所以在这里采用case when 2019 then 1 else 0 的写法

select u.user_id as buyer_id, join_date, sum(case when year(order_date) = '2019' then 1 else 0 end ) as orders_in_2019
from users u left join orders o # 因为我们要得出所有用户的数据，包括没有下订单的，换句话来说就是提取user表的所有数据，所以采用left join user 表
on u.user_id = o.buyer_id
group by 1,2