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

SELECT  t1.name AS u_n,
        t2.name AS c_n,
        t.date  AS date
FROM login AS t, user AS t1, client AS t2,
(
    SELECT  user_id,
            MAX(date) AS max_date
    FROM login
    GROUP BY  user_id
) AS t3
WHERE 
    t1.id = t.user_id -- 匹配姓名
    AND t2.id = t.client_id -- 匹配客户端
    AND t.date = t3.max_date -- 筛选最近登陆日期
    AND t.user_id = t3.user_id
ORDER BY t1.name