题解 | #筛选限定昵称成就值活跃日期的用户#
筛选限定昵称成就值活跃日期的用户
http://www.nowcoder.com/practice/2ed07ff8f67a474d90523b88402e401b
# 方法一：
# select distinct t3.uid,t3.nick_name,t3.achievement from (
# select t2.uid,t2.start_time,t2.score,u2.achievement,u2.nick_name,
# max(t2.start_time) over(partition by t2.uid) as min_date
# from (
# select t1.uid,t1.start_time,t1.score from
# (select e1.uid,e1.start_time,e1.score from exam_record as e1 ) t1
# union
# select e2.uid,e2.submit_time as start_time,e2.score from practice_record as e2
# ) t2 left join user_info as u2 on t2.uid=u2.uid
# where t2.uid in
# (select u1.uid from user_info as u1
# where LEFT(u1.nick_name,2)='牛客' and RIGHT(u1.nick_name,1)='号'
# and (u1.achievement between 1200 and 2500) )) t3
# where year(min_date)=2021 and month(min_date)=9
# 方法二：
select t2.uid,u2.nick_name,u2.achievement
from (
select t1.uid,t1.start_time,t1.score from
(select e1.uid,e1.start_time,e1.score from exam_record as e1 ) t1
union
select e2.uid,e2.submit_time as start_time,e2.score from practice_record as e2
) t2 left join user_info as u2 on t2.uid=u2.uid
where t2.uid in
(select u1.uid from user_info as u1
where LEFT(u1.nick_name,2)='牛客' and RIGHT(u1.nick_name,1)='号'
and (u1.achievement between 1200 and 2500) )
group by t2.uid,u2.nick_name,u2.achievement
having year(max(t2.start_time))=2021 and
month(max(t2.start_time))=9
