oracle 选择重复记录,oracle 重复记录

表:-- Create table

create table LOCATION

(

LOC            NVARCHAR2(15) not null,

DESCRIPTION    NVARCHAR2(40),

USERID         NVARCHAR2(20),

CREATEBY       VARCHAR2(30),

CREATETIME     DATE,

MODIFYBY       VARCHAR2(30),

LASTMODIFYTIME DATE,

STOREAREA_CODE VARCHAR2(4),

STOREKEEPER    VARCHAR2(20)

)

一。查找重复记录

1。查找全部重复记录

Select * From location l Where l.storekeeper In (Select l.storekeeper From location Group By l.storekeeper Having Count(*)>1)

2。过滤重复记录(只显示一条)

Select * From location l Where rowid In (Select Max(rowid) From location l Group By l.storekeeper)

二。删除重复记录

1。删除全部重复记录(慎用)

Delete location l Where l.storekeeper In (Select l.storekeeper From location l Group By l.storekeeper Having Count(*)>1)

2。保留一条

Delete location Where rownum Not In (Select Max(rownum) From location l Group By l.storekeeper)

(原创)