SQL143每份试卷每月作答数和截止当月的作答总数

发表于 分类于
这是文章开头，显示在主页面，详情请点击此处。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
    exam_id,
    start_month,
    month_cnt,
    sum(month_cnt) over(partition by exam_id order by start_month) cum_exam_cnt
from 
    (select 
        exam_id,
        # concat(year(start_time),right(substring_index(start_time,'-',2),2)) start_month,
        date_format(start_time,'%Y%m') start_month,
        count(*) month_cnt
    from exam_record
    group by exam_id,start_month
    )table1 
;