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
Table: Users+----------------+---------+| Column Name | Type |+----------------+---------+| user_id | int || join_date | date || favorite_brand | varchar |+----------------+---------+此表主键是 user_id。表中描述了购物网站的用户信息,用户可以在此网站上