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

with t1 as (
    select user_id, max(date) as date
    from login
    group by user_id
),
# 在这里我刚开始直接用的date in (select date from t1),这样是不对的，应该使用连接，连接的条件是用户id相同并
# 且日期也相同才能正确
t2 as (
    select t1.user_id, login.client_id, t1.date
    from t1
    left join login
    on t1.date = login.date and t1.user_id = login.user_id
),
t3 as (
    select a.name as u_n, t2.client_id, t2.date
    from t2
    left join user as a
    on t2.user_id = a.id
)
select u_n, b.name as c_n, date
from t3
left join client as b
on t3.client_id = b.id
order by u_n;