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

1. 首先在子查询连接题中的几个表并中选出所有需要的字段，并用row_number根据name分组汇总并根据date从大到小排序标号。然后从子查询中选出所需字段并限制标号为1。

select name, c_name, date from
(select u.name, c.name as c_name, l.date, 
 row_number() over (partition by u.name order by l.date desc) as ranking
from login as l, user as u, client as c
where l.user_id = u.id and l.client_id = c.id) as temp
where ranking = 1

2. 在子查询中连接login和user两个表并根据user_id分组汇总，选出user_id和date的分组最大值。然后连接login，user，client和子查询结果，使得login表的范围限制在子查询结果的范围内，最后选出所需字段并按user_name排序。

select u.name as u_n, c.name as c_n, temp.latest
from login as l, user as u, client as c,
(select l2.user_id, max(l2.date) as latest
from login as l2, user as u2
where l2.user_id = u2.id
group by l2.user_id) as temp
where l.user_id = temp.user_id and l.client_id = c.id and
      l.user_id = u.id and l.date = temp.latest
order by u.name