题解 | #删除记录（二）#

大致两种做法：

第一种：order + limit <删除的条数>

# where submit_time is null 
#     or timestampdiff(minute,start_time,submit_time) < 5
# order by start_time asc
# limit 3

注意：有一点需要注意 单纯用order by start_time排序的话记录处于同一个rank，其他排序默认按插入时或者说时自增id进行排序，故limit x会按顺序挑选最上面的x条记录时，id为3的记录不会被误删。

第二种做法：在where子句中筛选出要删除的记录，记录作为子查询

delete from exam_record
where
    id in (select distinct id
                from
                    (select distinct *,dense_rank() over(order by start_time asc) ranking
                    from exam_record
                    where submit_time is null 
                        or timestampdiff(minute,start_time,submit_time) < 5
                    order by start_time,submit_time asc
                    limit 3)x
                    )

下面是我一开始提交的错误代码，犯的错误就是没有注意到第一种方法考虑的那个点：

delete from exam_record
where id in (
			select distinct id
             from(select distinct *, dense_rank() over(order by id asc) ranking
                  from exam_record
                  where submit_time is null
                        or timestampdiff(minute,start_time,submit_time) < 5)x
             where ranking <=3
                     )