sql 对查询结果去重，对重复数据update打标记

 第一种方法：

查找重复数据里最小的id，然后根据id筛选出非最小id的，打标记

update gate_fayanzhongxin set match_type = 'FULL_MATCH_REPETE' 
WHERE match_type = 'FULL_MATCH' 
and id NOT IN (

      SELECT min(id)  FROM gate_fayanzhongxin where match_type = 'FULL_MATCH' 
GROUP BY fayan_unique_id
            
     )
           

 

 第二种方法：

 1.先分组查询出来重复的

SELECT substring(mgr_addvcd, 1, 6), gate_name from yg_gate_base_b
where  mgr_addvcd like  '3408%' 

group by substring(mgr_addvcd, 1, 6), gate_name


having count(*) > 1

 

2.再查出来重复的所有数据

select * from yg_gate_base_b  where (substring(mgr_addvcd, 1, 6), gate_name) in (

SELECT substring(mgr_addvcd, 1, 6), gate_name from yg_gate_base_b
where  mgr_addvcd like  '3408%' 

group by substring(mgr_addvcd, 1, 6), gate_name


having count(*) > 1
)

 

3.对重复的数据，筛选出要标记的

select * from yg_gate_base_b  where (substring(mgr_addvcd, 1, 6), gate_name) in (

SELECT substring(mgr_addvcd, 1, 6), gate_name from yg_gate_base_b
where  mgr_addvcd like  '3408%' 

group by substring(mgr_addvcd, 1, 6), gate_name


having count(*) > 1
)
and  geo_batch_update_flag in ('GATE_NOT_MATCH','FAYAN_NOT_MATCH_BATCH_INSERT','GATE_NOT_MATCH_BATCH_INSERT','FAYAN_NOT_MATCH_DELETE')

ORDER BY gate_name

 

4. 标记

 

update yg_gate_base_b set geo_data_status = 'D', geo_fayan_gate_repete = 'REPETE' where id in(

select id from yg_gate_base_b  where (substring(mgr_addvcd, 1, 6), gate_name) in (

SELECT substring(mgr_addvcd, 1, 6), gate_name from yg_gate_base_b
where  mgr_addvcd like  '3408%' 

group by substring(mgr_addvcd, 1, 6), gate_name


having count(*) > 1
)
and  geo_batch_update_flag in ('GATE_NOT_MATCH','FAYAN_NOT_MATCH_BATCH_INSERT','GATE_NOT_MATCH_BATCH_INSERT','FAYAN_NOT_MATCH_DELETE')

ORDER BY gate_name
)