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

思路：本体有两种思路可以求解

第一种思路：

1. 先根据用户分组，查询出每个用户登录的最新日期

SELECT user_id, max(date) AS date
FROM login
GROUP BY user_id

2. 根据题意可得我们需要查询出 client_id，所以我们将第一步的表与login表再此连接，取出client_id

SELECT t1.user_id, t1.client_id, t1.date
FROM login AS t1, (SELECT user_id, max(date) AS date FROM login GROUP BY user_id) AS t2
where t1.user_id = t2.user_id AND t1.date = t2.date

3. 再将第二步的表与user、client表连接，取出需要的数据，并根据user.name进行排序

SELECT u.name AS u_n, c.`name` AS c_n, uc.date AS date
FROM user AS u, client AS c, (SELECT t1.user_id, t1.client_id, t1.date
															FROM login AS t1, (SELECT user_id, max(date) AS date FROM login GROUP BY user_id) AS t2
where t1.user_id = t2.user_id AND t1.date = t2.date) AS uc
WHERE u.id = uc.user_id AND uc.client_id = c.id
ORDER BY u.name

第二种思路： 直接连接user、client、login表，然后使用IN关键字代入第一种思路的第一步中，查询出login.user_id，login.date，最后查询出需要的数据

select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name;