Mysql 解法, 执行用时: 36ms, 内存消耗: 6412KB, 提交时间: 2022-01-03

select a.uid,b.level,b.register_time,max(a.score) as max_score from exam_record a
inner join user_info b
on a.uid=b.uid
inner join examination_info c
on a.exam_id=c.exam_id
GROUP by  a.uid,b.level,b.register_time
having a.uid in(select a.uid from exam_record a
inner join user_info b
on a.uid=b.uid
inner join examination_info c
on a.exam_id=c.exam_id
where b.job='算法' and c.tag='算法' and
DATE_FORMAT(a.submit_time,'%Y%m%d')=DATE_FORMAT(b.register_time,'%Y%m%d'))
order by max_score desc
limit 6,3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-12-17

# register and take the test
# select distinct er.uid
# from exam_record er, user_info ui, examination_info ei
# where er.uid = ui.uid and er.exam_id = ei.exam_id and tag = '算法' 
# and date_format(register_time,'%Y%m') = date_format(submit_time,'%Y%m')


# select er.uid, level, register_time,max(score) max_score
# from exam_record er, user_info ui, examination_info ei
# where er.uid = ui.uid and er.exam_id = ei.exam_id
# and er.uid in 
# (select distinct er.uid
# from exam_record er, user_info ui, examination_info ei
# where er.uid = ui.uid and er.exam_id = ei.exam_id and tag = '算法'  and job = '算法'
# and date_format(register_time,'%Y%m') = date_format(submit_time,'%Y%m')
# )
# group by er.uid, level, register_time
# order by max_score desc
# limit 6,3




with t as (
    select t.uid,level,register_time,start_time,score,tag
    from user_info t join examination_info t1 join exam_record t2 on t.uid = t2.uid and t1.exam_id = t2.exam_id
    where job = '算法'
)
select uid,level,register_time,max_score
from 
(select *,dense_rank()over(order by max_score desc) as rn
from
(select uid,level,register_time,max(score) as max_score
from t
WHERE uid in (select uid from t where datediff(date(register_time),date(start_time))=0 and tag='算法')
group by uid) t1) t2
where rn BETWEEN 7 and 9 and max_score is not null

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-01-27

select er.uid,ui.level,ui.register_time,max(er.score) as max_score
from exam_record er
inner join examination_info ei on er.exam_id=ei.exam_id
inner join user_info ui on er.uid=ui.uid
where ui.job='算法'
and ei.tag='算法'
and date(ui.register_time)=date(er.submit_time)
group by er.uid
order by max_score desc
limit 6,3;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2022-01-24

SELECT DISTINCT
    uid,
    level,
    register_time,
    MAX(score) OVER(PARTITION BY uid) AS max_score
FROM exam_record
JOIN user_info USING(uid)
WHERE uid IN(
    SELECT uid
    FROM exam_record
    JOIN user_info USING(uid)
    JOIN examination_info USING(exam_id)
    WHERE job = '算法'#1.求职方向为算法工程师
    AND (DATEDIFF(register_time,submit_time)=0 AND tag = '算法')#2.注册当天就完成了算法类试卷
)
ORDER BY max_score DESC#3.按参加过的所有考试最高得分排名
LIMIT 6,3#4.返回第7~9条

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2021-12-13

select a.uid,level,register_time,max(score) over(partition by a.uid) as max_score
from user_info a left join exam_record b on a.uid=b.uid
left join examination_info c on b.exam_id=c.exam_id
where job='算法' and substr(register_time,1,7)=substr(submit_time,1,7) 
and tag='算法'
order by max_score desc
limit 6,3