【大厂真题】SQL22题解 | 每天登陆最早的用户的内容喜好

# 需求分析注：如果当天出现多个用户同时最早登录，那么这些用户均需要输出
# 输出日期、用户id、用户内容喜好，结果按照日期升序排序

SELECT DATE_FORMAT( a.log_time,'%Y-%m-%d') as log_day,
a.user_id,
c.hobby
FROM 
(
        SELECT user_id,log_time
        from login_tb 
) a
INNER JOIN 
(
        SELECT DATE_FORMAT( log_time,'%Y-%m-%d')  as log_time,min(log_time) as min_login_time
        from 
        login_tb 
        group by DATE_FORMAT( log_time,'%Y-%m-%d')   
) b
on a.log_time=b.min_login_time
left join user_action_tb c  # 表名
on a.user_id=c.user_id       # 关联字段
order by log_day asc