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

-- 先求出用户最近登录日期
WITH max_date AS(
    SELECT user_id,MAX(date) m_date
    FROM login
    GROUP BY user_id
),
-- 再去匹配用户的client_id
c_u_date AS(
    SELECT l2.user_id,l2.m_date,l1.client_id
    FROM login l1
    JOIN max_date l2
    ON l1.user_id = l2.user_id
    AND l1.date = l2.m_date

)
-- 再将此CTE与另外两个表相连接
SELECT t2.name u_n,t3.name c_n,t1.m_date AS date
FROM c_u_date t1
JOIN user t2
ON t1.user_id = t2.id
JOIN client t3
ON t1.client_id = t3.id
ORDER BY t2.name