题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/market-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
– 1、先将Orders表和Items表内连接,再内连接上Users表,并筛选出order_date =‘2019’
– 2、再按temp.buyer_id,u.join_date分组,得到每个用户的注册日期以及在 2019 年作为买家的订单总数,构成temp表
– 3、最后使用Users左连接temp表,将订单总数为null的设置为0
select u1.user_id as buyer_id ,u1.join_date,ifnull(temp2.counts,0)as orders_in_2019
from Users as u1 left join (
select temp.buyer_id as buyer_id,u.join_date,count(temp.item_brand)as counts
from (
select order_id,date_format(order_date,"%Y") as order_date ,o.item_id,buyer_id,item_brand
from Orders as o inner join Items as i
on o.item_id = i.item_id
)temp inner join Users as u
on temp.buyer_id=u.user_id
where order_date ='2019'
group by temp.buyer_id,u.join_date
)temp2
on u1.user_id = temp2.buyer_id
也有简洁解法,当时没有想到
select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
select buyer_id, count(order_id) cnt
from Orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id