题解 | #牛客每个人最近的登录日期(二)#

# t表计算每个用户user_id最近登录的日期
# h表连接login增加client_id
# h表连接user和client找到u_n，c_n

select u.name as u_n,c.name as c_name,h.date
from 
(select t.user_id,t.date,client_id
from (select user_id,max(date) as date
from login
group by user_id)t left join login l on l.user_id=t.user_id and l.date=t.date)h
left join user u on h.user_id=u.id
left join client c on h.client_id=c.id
order by u.name 



# select user.name, client.name, d date
# from user,
# client,
# (select *, max(date) over(partition by user_id) d from login) t
# where user.id = t.user_id
# and client.id = t.client_id
# and t.date = t.d
# order by user.name