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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ruiguang21/article/details/80944778

--查看表中重复的记录  

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')
 

展开阅读全文

没有更多推荐了,返回首页