开窗函数| #牛客每个人最近的登录日期(二)#

子查询

select 
    distinct u.name as u_n
    ,c.name as c_n
    ,date 
from
    (
        select *
        from login
        where id in 
            (
                select max(id) #子查询筛选出最近登录的id
                from login
                GROUP by user_id
            ) 
    ) l
join `user` u
    on l.user_id = u.id
join client c
    on l.client_id = c.id
order by u_n asc 

开窗函数

select 
    distinct u.name as u_n
    ,c.name as c_n
    ,date 
from
    (
        select *,
        max(id) over(PARTITION BY user_id ) as last_in
        from login
    ) l
join `user` u
    on l.user_id = u.id
join client c
    on l.client_id = c.id
where l.id = last_in 
order by u_n asc