over函数 去重多个字段和一个字段 用于统计

  
     select 
   ot.leave_time,
   ot.safe_time_one,ot.safe_time_two,
   ot.user_agentid,ot.chinese_name,
   ot.safe_time_three,
   ot.rep_off,
   ot.plus_aux
   from 
   
   (
   select t2.leave_time,
   t.safe_time_one,t.safe_time_two,
   t.user_agentid,t.chinese_name,
   t.safe_time_three,
   t3.rep_off,
   row_number() over(partition by t.user_agentid order by t2.create_time desc) as plus_aux
    from t_ows_zb_person_carding t 
    inner join t_ows_zb_safeguarddetailes t1
   on t.political_trial_id=t1.id
   inner join t_ows_zb_operator_punch t2
   on t2.psc_id=t.id 
   inner join t_ows_zb_sg_distribution t3
   on t3.safeguard_id=t1.id
   where t1.id='20190901'
   order by t2.create_time desc  ) ot 
   
   where ot.plus_aux=1

 

  
  select  a.user_agentid,a.sum_01                                        --取一条

from (

select t.user_agentid,count(*)  over(partition by t.user_agentid) as sum_01

from t_ows_zb_person_carding t

) a where a.sum_01=1

  
  select 
  t2.user_agentid,count(*)
  from (
  select t.user_agentid, 
  rank() over(partition by t.user_agentid,t.chinese_name order by t.id) 
  as group_psx from t_ows_zb_person_carding t )t2
  where t2.group_psx=1 and t2.user_agentid='m00509514'
  group by  t2.user_agentid