# 方法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