首先申明下,这篇文章并不是严格意思上的原创,是昨天项目上有一个这样的需求,要用到row_number() over()的功能。我也是借鉴了别人的博文的https://www.cnblogs.com/yhzh/p/6222580.html
以下是我写的sql
SELECT
a.ui_class_id as classId,
if(@type=a.ui_class_id OR (@type is null AND a.ui_class_id is null),@num:=@num+1,@num:=1) as row_number,
@type:=a.ui_class_id
FROM
(SELECT ui_class_id
FROM db_teaching_pad_result.tb_course_index_anlz
WHERE DATE_FORMAT(dt_event_time, "%Y-%m-%d") BETWEEN '2017-08-02'AND '2018-08-02'
group by ui_class_id,date(dt_event_time) ORDER BYui_class_id, dt_event_time DESC
) a,
(SELECT @num := 0, @type := null) b
对应查询的结果集