例子:

1、

SELECT uid,a.exam_id,score,

MIN(score) OVER(PARTITION BY exam_id) min_x, #求每类试卷的得分最小值

MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值

FROM exam_record a

LEFT JOIN examination_info b ON a.exam_id=b.exam_id

WHERE difficulty='hard' #难度为'hard'

AND score IS NOT NULL; #分数不为空

输出结果:

sql必备知识(每日SQL自学知识点)(1)

2、

SELECT exam_id,DATE_FORMAT(start_time,'%Y%m')start_month, COUNT(start_time) month_cnt,

SUM(COUNT(start_time))OVER(Partition by exam_id ORDER BY DATE_FORMAT(start_time,'%Y%m'))cum_exam_cnt

FROM exam_record

GROUP BY exam_id,start_month;

输出结果

sql必备知识(每日SQL自学知识点)(2)

,