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

# 方法1：根据每个用户的先后登录时间进行排名，时间最晚的排名第1，统计出所有用户排名第1的记录-- 窗口函数
# with t as (
#   SELECT u.name u_n,c.name c_n,date
#   ,dense_rank()over(partition by l.user_id order by date desc) posn
#   from login l
#   inner join user u
#   on l.user_id=u.id
#   inner join client c
#   on l.client_id=c.id
# )

# SELECT u_n,c_n,date
# from t
# where posn =1
# order by u_n

-- 方法2：筛选每个用户的最晚登陆记录
# 1.查询出每个用户最近登陆时间 -- max
# select user_id,max(date)
# from login
# group by user_id
# 2.连接多个表，筛选出最近登陆的用户记录 -- 子查询
select u.name,c.name,date
from login l
inner join user u
on l.user_id=u.id
inner join client c
on l.client_id=c.id
where (user_id,date) in (
  select user_id,max(date)
  from login
  group by user_id
)
order by u.name