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

首先找到每个user的最近登录日期recent和对应client_id，这里不能直接把client_id放在group by中，因此用where子查询。

然后将recent和user表，和client表连接。

with recent as (select
    user_id,
    client_id,
    date
from login l1
where date = (
    select max(l2.date)
    from login l2
    where l1.user_id = l2.user_id
    ))

select 
    u.name  as u_n,
    c.name  as c_n,
    r.date
from recent r
join user u on r.user_id = u.id
join client c on r.client_id = c.id
order by u_n