select
a.user_id buyer_id,
a.join_date,sum(if(year(b.order_date)='2019',1,0)) orders_in_2019
from Users a
leftjoin Orders b on a.user_id = b.buyer_id
groupby a.user_id
方法2
select
a.user_id buyer_id,
a.join_date,sum(casewhenyear(b.order_date)='2019'then1else0end) orders_in_2019
from Users a
leftjoin Orders b on a.user_id = b.buyer_id
groupby user_id
方法3
select
a.user_id buyer_id,
a.join_date,sum(casewhen order_date like'2019%'then1else0end) orders_in_2019
from Users a
leftjoin Orders b on a.user_id = b.buyer_id
groupby user_id
方法4
select
a.user_id buyer_id,
a.join_date,count(order_id) orders_in_2019
from Users a
leftjoin Orders b on a.user_id = b.buyer_id andyear(b.order_date)='2019'groupby user_id