提出oracle表中重复行,Oracle 查找删除表中重复行的SQL

-- This query is not editable, but generally faster

Select

in_type,

in_type_serial,

count(*)

from ec_product_in

group by

in_type,

in_type_serial

having count(*) > 1

-- This query is editable, but generally slower

Select A1.id

from ec_product_in A1

where exists (Select 'x' from ec_product_in A2

where A1.in_type = A2.in_type

and a1.in_type_serial= A2.in_type_serial

and A1.ROWID <> A2.ROWID)

-- To delete all but one of each duplicate row,

-- change the first line to 'Delete'

-- and change the '<>' to '>' (keeps first duplicate)

-- or '

找出需要删除的行

/* Formatted on 2011-8-17 9:59:53 (QP5 v5.114.809.3010) */

-- This query is editable, but generally slower

SELECT A1.*, A1.ROWID

FROM ec_product_in A1

WHERE EXISTS

(SELECT 'x'

FROM ec_product_in A2

WHERE A1.in_type = A2.in_type

AND a1.in_type_serial = A2.in_type_serial

AND A1.ROWID <> A2.ROWID)

-- To delete all but one of each duplicate row,

-- change the first line to 'Delete'

-- and change the '<>' to '>' (keeps first duplicate)

-- or '

AND a1.id NOT IN

(SELECT A1.id

FROM ec_product_in A1

WHERE EXISTS

(SELECT 'x'

FROM ec_product_in A2

WHERE A1.in_type = A2.in_type

AND a1.in_type_serial =

A2.in_type_serial

AND A1.ROWID > A2.ROWID)-- To delete all but one of each duplicate row,

-- change the first line to 'Delete'

-- and change the '<>' to '>' (keeps first duplicate)

-- or '

)