都行可以少年：with t1 as(select exam_id, timestampdiff(second, start_time, submit_time) df1, row_number() over(partition by exam_id order by timestampdiff(second, start_time, submit_time) desc) rk1 from exam_record where submit_time is not null), t2 as(select exam_id, timestampdiff(second, start_time, submit_time) df2, row_number() over(partition by exam_id order by timestampdiff(second, start_time, submit_time)) rk2 from exam_record where submit_time is not null) select exam_id, duration, release_time from t1 join t2 using(exam_id) join examination_info using(exam_id) where rk1 = 2 and rk2 = 2 and (df1-df2)*2>=duration*60 order by 1 desc;

fog~：ound(count(*) / day(last_day(submit_time)) ,3) as avg_day_q_cnt 这句话执行不出来把，这个day(last_day(submit_time))这个没有进行分组，也就是这个多条数据，你需要讲这个取均值，这样则为一条数据