题解 | #牛客每个人最近的登录日期(二)#
牛客每个人最近的登录日期(二)
http://www.nowcoder.com/practice/7cc3c814329546e89e71bb45c805c9ad
/利用开窗函数可以避免group by对暂时变量的限制/ /这道题一直没运行出来的原因是login表的user_id对应user表的id，client_id对应client表的id。一开始我一直用三个表的id去匹配/
SELECT u.name as u_n,c.name as c_n,t1.date
FROM
(
SELECT id,user_id,client_id,date,
ROW_NUMBER()over(PARTITION BY user_id order by date DESC) as rank_num
FROM login
) t1
LEFT JOIN user u ON t1.user_id=u.id
LEFT JOIN client c ON t1.client_id=c.id
WHERE t1.rank_num=1
ORDER BY u_n;