知识点
- 主要思路就是先按照用户进行分组,日期最大为筛选条件
- 使用where(用户id, 日期) in新表进行筛选,新表进行分组选日期最大
- 使用窗口函数直接建立新表,按照用户分组把日期进行排序,排序=1就是所求
代码
1.where() in进行筛选
select u.name as u_n, c.name as c_n, l.date as date
from login as l
join user as u
on l.user_id = u.id
join client as c
on l.client_id = c.id
where(l.user_id, l.date) in(
select user_id, max(date)
from login
group by user_id)
order by u.name
2.窗口函数进行排序
select u.name as u_n, c.name as c_n, temp.date
from (
select user_id,
client_id,
date,
row_number() over (partition by user_id order by date desc) as date2
from login
) as temp
join client as c on c.id = temp.client_id
join user u on temp.user_id = u.id
where temp.date2 = 1
order by u.name