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

【实例简介】

【实例截图】

【核心代码】

--查询重复数据方法一

select COUNT(*) from HB_BORROWER_INFORMATION a

where (a.APPLICATION_CODE) in (

select APPLICATION_CODE from HB_BORROWER_INFORMATION group by APPLICATION_CODE

having

count(*) > 1)

and rowid not in (select min(rowid) from HB_BORROWER_INFORMATION group by APPLICATION_CODE having count(*)>1)

--查询重复数据方法二

SELECT

ROWID,

a.BORROWER_NO

FROM

HB_PLATFORM_CUSTOMER a

WHERE

a.rowid !=(

SELECT

MAX( ROWID )

FROM

HB_PLATFORM_CUSTOMER b

WHERE

a.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