# 思路：先筛选出最近登录的数据，再聚合去重，最后连接上用户名和客户端名表。用row_number筛选出日期最大值。

with t as
(
    select user_id,client_id,date,row_number() over (partition by user_id order by date desc) as r
    from login
)

select u.name,c.name,t.date
from t
     left join user u on t.user_id = u.id
     left join client c on t.client_id = c.id 
where r = 1
order by u.name