526互联

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

发布时间 2023-11-01 18:25:25作者: 布鲁布鲁sky

 

 

 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
)