题解 | #每份试卷每月作答数和截止当月的作答总数。#
每份试卷每月作答数和截止当月的作答总数。
https://www.nowcoder.com/practice/5f1cbe74c682485aa73e4c2b30f04a62
-- 这题还挺简单 -- 1、用group by求解每月作答数,用作临时表t # select # exam_id, # replace(date_format(start_time,"%Y-%m"),'-','') # as start_month, # count(start_time) as month_cnt # from exam_record # group by start_month, exam_id -- 2、用窗口函数求取截至当月作答数 select exam_id, start_month, month_cnt, sum(month_cnt) over(partition by exam_id order by start_month) as cum_exam_cnt from (select exam_id, replace(date_format(start_time,"%Y-%m"),'-','') as start_month, count(start_time) as month_cnt from exam_record group by start_month, exam_id) t