select u.name,c.name,l.date
from (select *, rank() over(partition by user_id order by date desc) t_rank from login) l
inner join
user u
on l.user_id = u.id
and l.t_rank=1
inner join
client c
on c.id=l.client_id
order by u.name

  • 使用窗口函数，找出最大值的同时还能保持login表的格式不变