题解 | #牛客每个人最近的登录日期(二)#
牛客每个人最近的登录日期(二)
http://www.nowcoder.com/practice/7cc3c814329546e89e71bb45c805c9ad
跟前面汇总每个部门最高薪水一样的,4种方法
我的方法:
with tmp as(
select l.user_id ui,date,u.name u_n,c.name c_n from login l join user u on l.user_id=u.id
join client c on c.id=l.client_id
)
select u_n,c_n,date from tmp join(select user_id,max(date)d from login
group by user_id)t1 on tmp.ui=t1.user_id and tmp.date=t1.d
order by u_n
max聚合窗口函数,比rank方便一点
select user.name, client.name, d date
from user,
client,
(select *, max(date) over(partition by user_id) d from login) t
where user.id = t.user_id
and client.id = t.client_id
and t.date = t.d
order by user.name
SQL题解 文章被收录于专栏
主要是为自己做个笔记