sql 对查询结果去重，按时间排序 只取一条

 第一种方法：

查找重复数据里最小的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
            
     )
           

 

查找重复数据里时间最小的， 打标记

update yg_gate_safety_identify a set data_status = 'D' where id in (
    select id from yg_gate_safety_identify identify
    where identify.create_time =
          (select min(create_time) from yg_gate_safety_identify b where identify.gate_id = b.gate_id GROUP BY gate_id having count(*) > 1)
)

 

 

第二种办法

 

pgsql left join关联查询 ，对右表 按时间排序，只取一条

SELECT u.id, u.name, o.order_date
FROM users u
LEFT JOIN (
    SELECT o.user_id,
           o.order_date,
           ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_date ASC) AS rn
    FROM orders o
) o ON u.id = o.user_id AND o.rn = 1;

 

 

 

 

 第二种方法：

 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
)