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

【思路】
1.最终得到的表里需要u_n,c_n，但是login表中只有user_id,client_id，因此首先要将三个表进行连接
2.要得到每个用户最近一次登录的日期、客户端等信息，需要用到group by
3.难点在于group by只能提取聚合函数与聚合列，这样子就不能得到客户端名字c_n
4.因此最后需要将group by得到的表跟第一步得到的总表进行连接来得到所有信息
【代码如下】
select t1.u_n, t1.c_n,t2.dat as date from
(select user_id, max(date) as dat from login group by user_id ) as t2
left join
(select l.user_id, u.name as u_n, c.name as c_n, l.date from
login as l left join user as u on l.user_id = u.id
left join client as c on l.client_id = c.id) as t1
on t2.user_id = t1.user_id
and t2.dat = t1.date
order by t1.u_n
【代码二】
select user.name as u_n, client.name as c_n,
login.date
from login
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name
思路与上述代码一样，只是使用了子查询来获得客户端信息。