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

第一种方法：先聚合，找除每个人最新一条记录，再将该表内连接到正常表中，使其只剩下最新一条记录。（讨论区还未看到这种方法，实际上和 where in 差不多，一个是在连接时就只保留最新记录，一个是连接后找到最新记录）

select
    b.name as u_n,
    c.name as c_n,
    a.date
from
    login a
    join (
        select
            user_id,
            max(date) as date
        from
            login
        group by
            user_id
    ) as t on a.user_id = t.user_id
    and a.date = t.date
    join user b on a.user_id = b.id
    join client c on c.id = a.client_id
order by
    u_n

第二种方法：窗口函数按照user 分块排序，最后从整理好的表中select想要的列，并在where处筛选最新一条记录，

select
    u_n,
    c_n,
    date
from
    (
        select
            b.name as u_n,
            c.name as c_n,
            a.date,
            row_number() over (
                partition by
                    a.user_id
                order by
                    a.date desc
            ) as rk
        from
            login as a
            join user as b on a.user_id = b.id
            join client as c on a.client_id = c.id
    ) as temp
where
    rk = 1
order by
    u_n asc