Shulou(Shulou.com)06/01 Report--

-- Test data

Create table test as select * from dba_objects

Insert into test select * from test

-- check to see if there is any duplicate data

Select * from test order by object_id

-- Delete with rowid

Select rowid,a.* from test an order by object_id

-- grouped according to rowid

Method (1) not in

DELETE FROM test WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM test GROUP BY object_id)

Method (2) in

DELETE FROM test a

WHERE ROWID IN (SELECT ROWID

FROM (SELECT row_number () over (PARTITION BY object_id ORDER BY ROWID) rn

A. *

FROM test a)

WHERE rn > 1)

-- method 3 exists

DELETE FROM test a

WHERE EXISTS (SELECT NULL

FROM test b

WHERE a.object_id = b.object_id

AND a.rowid

< b.rowid) --in的等价写法 DELETE FROM test a WHERE object_id IN (SELECT object_id FROM test b WHERE a.object_id = b.object_id AND a.rowid < b.rowid) --如果要保留3条重复数据 DELETE FROM test a WHERE ROWID IN (SELECT ROWID FROM (SELECT row_number() over(PARTITION BY object_id ORDER BY ROWID) rn, a.* FROM test a) WHERE rn >

3)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.