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

select
t2.u_n,
t2.c_n,
t2.date
from
(select
user_id,
max(`date`) as `date`
from login
group by user_id) as t1
inner join
(select
l.id,
l.user_id,
l.client_id,
l.date,
u.name as u_n,
c.name as c_n
from login as l inner join user as u
on l.user_id = u.id 
inner join client as c
on l.client_id = c.id
) as t2
on t1.user_id = t2.user_id and t1.date = t2.date
order by u_n

1.先求出最新登录的日期和用户id

select

user_id, -- 用户编号

max(`date`) as date -- 最新登录日期

from login

group by user_id

2.再求出所有的用户的，登录的设备名称，用户的姓名和登录日期

select

l.id,

l.user_id, -- 用户编号

l.client_id, -- 设备编号

l.date, --登录日期

u.name as u_n, -- 用户姓名

c.name as c_n -- 设备名称

from login as l inner join user as u

on l.user_id = u.id

inner join client as c

on l.client_id = c.id

3.将上面的两个结果关联

select

t2.u_n,

t2.c_n,

t2.date

from t1 inner t2

on t1.u_n = t2.u_n and t1.date = t2.date