oracle去除重复数据的三种方法(包含删除重复数据的sql语句)

--查询重复数据方法一select COUNT(*) from HB_BORROWER_INFORMATION awhere (a.APPLICATION_CODE) in (select APPLICATION_CODE from HB_BORROWER_INFORMATION group by APPLICATION_CODEhavingcount(*) > 1)and rowid not in (select min(rowid) from HB_BORROWER_INFORMATION group by APPLICATION_CODE having count(*)>1)--查询重复数据方法二SELECTROWID,a.BORROWER_NOFROMHB_PLATFORM_CUSTOMER aWHEREa.rowid !=(SELECTMAX( ROWID )FROMHB_PLATFORM_CUSTOMER bWHEREa.BORROWER_NO = b.BORROWER_NO);--删除重复DELETE from MCG_FLOW_TRANSACTION WHERE (ORDER_CODE) IN ( SELECT ORDER_CODE FROM MCG_FLOW_TRANSACTION GROUP BY ORDER_CODE HAVING COUNT(ORDER_CODE) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM MCG_FLOW_TRANSACTION GROUP BY ORDER_CODE HAVING COUNT(*) > 1);--查询重复数据方法二SELECT * FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY a.BID_ID ORDER BY a.LOAN_DATE DESC) rn, a.* FROM HB_BORROWER_INFORMATION_NU a ) WHERE rn>1