一、删除指定时间范围多余的重复记录(mobile为重复的值),只保留指定字段最小的记录
逻辑:
1.先去除所有mobile字段有重复的数据只保留id最小的一条作为结果集
2.删除除开这张虚拟表存在id以外的数据
delete from 表a where
a.id not in ( select dt.id from
( select min(id) as id from 表a
WHERE create_time >= '2023-02-01 00:00:00'
create_time <= '2023-02-17 12:10:23'
and certify_status=0
group by mobile ) dt )
二、根据指定字段自定义条件去重插入
1先查出指定条件的结果集a作为WHERE NOT EXISTS的判断条件,如果结果集不存在则通过
2.外层select查的字段(如22,33)为外层所需要查的结果集字段(需与insert插入的字段相同),内存select查重复数据,若查出则已存在该数据被NOT EXISTS判开,即内存查出的数据不进入外层select的结果集中,如此外层select结果集则完成了去重,再将结果集作为值插入
INSERT INTO proj_biz_qywx_group_ready_user (agent_id,dis_agent_id)
SELECT 22,33
FROM DUAL
WHERE NOT EXISTS (SELECT agent_id ,dis_agent_id FROM proj_biz_qywx_group_ready_user WHERE agent_id=22 AND dis_agent_id = 33)
三、根据指定字段自定义条件去重插入在mybatis中的效果呈现
<insert id="insertReadyGroupAgent" parameterType="ReadyGroupAgent" useGeneratedKeys="true" >
INSERT INTO 表a (agent_id,create_time,dis_agent_id,user_type,ready_group_id,qywx_user_id)
select * from (
<foreach collection="list" item="item" separator=" UNION ALL " >
SELECT #{item.agentId} agentId,#{item.createTime} createTime,#{item.disAgentId} disAgentId,
#{item.userType} userType,#{item.readyGroupId} readyGroupId,#{item.qywxUserId} qywxUserId FROM DUAL
</foreach>
) a where not exists (select agent_id,ready_group_id from 表a b
where a.agentId = b.agent_id and a.readyGroupId=b.ready_group_id)
</insert>