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

*方案1：login表+dense_rank窗口函数按date排序作为临时表t；联结user,client表，where过滤t.date_rank获得每个用户最近一次登录的记录

SELECT u.name AS u_n,c.name AS c_n,t.date
FROM (SELECT *,DENSE_RANK() OVER(PARTITION BY user_id
                                 ORDER BY date DESC) AS date_rank
      FROM login) t
INNER JOIN user u
ON t.user_id = u.id 
INNER JOIN client c 
ON t.client_id = c.id 
WHERE t.date_rank = 1
ORDER BY u.name ASC;

*方案2：同样三表联结，where子查询过滤，过滤的子句采用MAX()函数求出date最大并传递到子查询之外。

SELECT u.name  AS u_n,c.name AS c_n,l1.date 
FROM login l1
INNER JOIN user u 
ON l1.user_id = u.id 
INNER JOIN client c 
ON l1.client_id = c.id 
WHERE l1.date IN (SELECT MAX(l2.date)
                FROM login l2
                WHERE l1.user_id = l2.user_id 
                GROUP BY l2.user_id)
ORDER BY u.name ASC;