create table INFO.T_CI_CUSTUSERREL_L ( CUSTID CHAR(14) not null, USERID CHAR(14) not null, RELTYPEID NUMBER(4) not null, OPTRID VARCHAR2(20) not null, OPTDATE DATE not null, REMARK VARCHAR2(128) ); CUSTID USERID RELTYPPEID OPTRID OPTDATE LC060100043699 LU060100043700 5000 L0A010016 2006-01-03 10:24:59 LC00J000035542 LUY10000555258 5000 Y00000SYS 2007-10-02 15:25:50 LC060100044211 LU060100044212 5000 L0A030003 2006-01-03 11:01:11
首先确认你的删除不会对以后的数据产生影响! delete from INFO.T_CI_CUSTUSERREL_L where rownum <> (select max(rownum) from INFO.T_CI_CUSTUSERREL_L);
commit;select * from INFO.T_CI_CUSTUSERREL_L;
DELETE FROM INFO.T_CI_CUSTUSERREL_L WHERE ROWNUM< (SELECT MAX(ROWNUM) from INFO.T_CI_CUSTUSERREL_L);
delete from x where rowid in ( select rd from ( select rowid rd ,row_number() over(partition by CUSTID, USERID, RELTYPPEID, OPTRID order by rowid) rn from x ) x where rn > 1 )
(
CUSTID CHAR(14) not null,
USERID CHAR(14) not null,
RELTYPEID NUMBER(4) not null,
OPTRID VARCHAR2(20) not null,
OPTDATE DATE not null,
REMARK VARCHAR2(128)
);
CUSTID USERID RELTYPPEID OPTRID OPTDATE
LC060100043699 LU060100043700 5000 L0A010016 2006-01-03 10:24:59
LC00J000035542 LUY10000555258 5000 Y00000SYS 2007-10-02 15:25:50
LC060100044211 LU060100044212 5000 L0A030003 2006-01-03 11:01:11
delete from INFO.T_CI_CUSTUSERREL_L
where rownum <> (select max(rownum) from INFO.T_CI_CUSTUSERREL_L);
commit;select * from INFO.T_CI_CUSTUSERREL_L;
WHERE ROWNUM< (SELECT MAX(ROWNUM) from INFO.T_CI_CUSTUSERREL_L);
from x
where rowid in
(
select rd
from (
select rowid rd
,row_number() over(partition by CUSTID, USERID, RELTYPPEID, OPTRID order by rowid) rn
from x
) x
where rn > 1
)