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

最新推荐文章于 2024-07-11 16:22:36 发布

原创最新推荐文章于 2024-07-11 16:22:36 发布 · 777 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种SQL方法来查找并去除表中的重复记录。首先通过联表查询结合分组统计找出所有重复的记录，然后使用窗口函数ROW_NUMBER()进行筛选，确保每个分组只保留一条最新记录。

--查看表中重复的记录

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