提交
select user_id as buyer_id, join_date, ifnull(cnt, 0) as orders_in_2019
from Users
left join (
select buyer_id, COUNT(order_id) as cnt
from Orders
where date(order_date) between '2019-01-01' and '2019-12-31'
group by buyer_id
) as tmp_table
on user_id = tmp_table.buyer_id
关键点
- ifnull(cnt, 0) 函数
- COUNT() 和 group by()
- 使用 left join
- 构造中间临时表tmp_table:直接从Orders表中查询出来buyer_id(比如,下图buyer_id=2的有两个,但Users表中的user_id=2的只有一个) 与 Users表中的user_id 是多对一的关系,所以需要COUNT搭配group by聚合一下,变成一对一的关系,这样才能进行on user_id = tmp_table.buyer_id。