Oracle 查看表中重复的记录，以及去除表中的重复记录

曾经终败给现在 2022-05-21 11:55 296阅读 0赞

\--查看表中重复的记录

select

tscw.L\_SEC\_KEY as L\_BOND\_KEY ,---债券内码  
      to\_date(twcb.b\_info\_carrydate,'yyyyMMdd') as D\_BEGIN,--计息起始日  
       count(\*)  
     from twind\_cbondcf twcb left join TDW\_SEC\_CODE\_WIND tscw  
    on twcb.s\_info\_windcode=tscw.vc\_wind\_code where tscw.L\_SEC\_KEY is not null  
    group by tscw.L\_SEC\_KEY,twcb.b\_info\_carrydate having count(\*)>1  
\--去除表中的重复记录  
 select t.\* from (     
 select  
      row\_number() over(partition by tscw.L\_SEC\_KEY,twcb.b\_info\_carrydate order by twcb.opdate desc ) rn,  
      tscw.L\_SEC\_KEY as L\_BOND\_KEY ,---债券内码  
      to\_date(twcb.b\_info\_carrydate,'yyyyMMdd') as D\_BEGIN,--计息起始日  
      to\_date(twcb.b\_info\_enddate,'yyyyMMdd') as D\_END,--计息截止日  
      twcb.b\_info\_couponrate as L\_COUPONRATE,--票面利率(%)  
      to\_date(twcb.b\_info\_paymentdate,'yyyyMMdd') as D\_PAYMENT,--现金流发放日  
      twcb.b\_info\_paymentinterest as  L\_PAYMENT\_INTEREST,--期末每百元面额应付利息  
      twcb.b\_info\_paymentparvalue as L\_PAYMENT\_PARVALUE,--期末每百元面额应付本金  
      twcb.b\_info\_paymentsum as L\_PAYMENT\_SUM ,--期末每百元面额现金流合计  
      twcb.opdate as D\_OP --入库时间  
      from twind\_cbondcf twcb left join TDW\_SEC\_CODE\_WIND tscw  
      on twcb.s\_info\_windcode=tscw.vc\_wind\_code where tscw.L\_SEC\_KEY is not null ) t  
      where t.rn=1  
      and  t.L\_BOND\_KEY='10113136' and  to\_date(to\_char(t.D\_BEGIN,'yyyyMMdd'),'yyyyMMdd')=to\_date('20151026','yyyyMMdd')