背景:task表中保存有车的代办工单的数据,因为历史原因,该数据表中保存有同一个车同一个status的多条数据,现在需要将历史数据都删掉,只保留同一个车同一个状态下的最新一条记录即可,那么这个sql该怎么写呢?
实现这个需求,我们先要了解几个MySQL的函数:
第一个函数是group by和group_concat。group_concat的作用是将group by产生的同一个分组中的值连接起来,返回一个字符串结果。因为我们是想获取非最新的数据id,然后根据id将数据进行删除。我们可以结合group_concat和order by实现目的,当然order by不是放在where之后,而是放在group_concat的括号里面(此处如果放在where的后面,是没有排序效果的)。
第二个函数,如果有字符串是123,456,789,如果我想获取第一个逗号以后的内容,该怎么办,这时候我们要用到LOCATE和SUBSTRING函数,例如:SELECT SUBSTRING('123,456,789',LOCATE(',','123,456,789')+1),结果为456,789。补充一下,如果想获取逗号之前的内容可以使用SUBSTRING_INDEX函数,示例为:SELECT SUBSTRING_INDEX('111,222,333,444,555,666',',',3),结果为获取第三个逗号之前的内容,即111,222,333
下面开始整合sql:
SELECT
SUBSTRING(GROUP_CONCAT(id ORDER BY created_at DESC), LOCATE(',', GROUP_CONCAT(id ORDER BY created_at DESC)) + 1) AS ids,
GROUP_CONCAT(id ORDER BY created_at DESC) AS groupIds, GROUP_CONCAT(created_at) AS groupCreatedAt,
carId, COUNT(*) AS num
FROM
task
WHERE
type = 410 AND is_deleted = 0 AND status = 11
GROUP BY
carId
HAVING
num > 1
ORDER BY id ASC
结果集ids即排除了最新一条数据id,剩下我们将要删除的ids,直接根据id执行删除操作就可以了。