Mysql5分组去重sql

1.重复数据sql校验

select id, guest_id, guest_plan_id, count(*)
from hm_guest_guest_plan
where del_flag = 1
GROUP BY guest_id, guest_plan_id
HAVING count(*) > 1
ORDER BY guest_plan_id;

2.获取重复数据

SET @rn = 0;
SET @groupVal = NULL;
SELECT *
from (
         SELECT @rn := IF(@groupVal = CONCAT(t.guest_id, '-', t.guest_plan_id), @rn + 1, 1) rn,
                @groupVal := CONCAT(t.guest_id, '-', t.guest_plan_id)                       type,
                t.*
         FROM hm_guest_guest_plan t
         where t.del_flag = 1
         ORDER BY guest_id,guest_plan_id,id
     ) temp
where temp.rn >1
ORDER BY temp.id
;

3.删除重复数据的id 再使用校验sql进行校验是否还存在重复数据

4.批量插入的业务如果数据不存在唯一性校验在业务中一定要做去重校验 防止因业务缺失导致批量插入数据时出现重复数据