oracle sql删除重复数据,求一条 PL/SQL删除重复数据中的一条数据

本帖最后由 YuBinTAMU 于 2014-2-7 12:00 编辑

BEGIN

CREATE TABLE ITP_TEST

( HM         VARCHAR2(4),

ID         NUMBER(3),

NAME         VARCHAR2(10),

DT         DATE

);

INSERT INTO ITP_TEST VALUES('A',1,'W', DATE '2012-01-04');

INSERT INTO ITP_TEST VALUES('A',2,'S', DATE '2013-05-05');

INSERT INTO ITP_TEST VALUES('B',2,'D', DATE '2014-01-01');

INSERT INTO ITP_TEST VALUES('B',3,'H', DATE '2011-05-23');

INSERT INTO ITP_TEST VALUES('C',3,'W', DATE '2010-04-01');

INSERT INTO ITP_TEST VALUES('C',4,'V', DATE '2014-01-03');

INSERT INTO ITP_TEST VALUES('D',1,'Q', DATE '2013-04-07');

INSERT INTO ITP_TEST VALUES('D',2,'S', DATE '2012-05-04');

INSERT INTO ITP_TEST VALUES('E',1,'S', DATE '2012-06-09');

INSERT INTO ITP_TEST VALUES('E',1,'A', DATE '2012-04-11');

INSERT INTO ITP_TEST VALUES('E',3,'W', DATE '2011-09-28');

INSERT INTO ITP_TEST VALUES('F',5,'V', DATE '2013-09-29');

INSERT INTO ITP_TEST VALUES('F',2,'E', DATE '2012-07-15');

INSERT INTO ITP_TEST VALUES('F',1,'I', DATE '2012-06-01');

--   28 rows will be inserted.

END;

/

SELECT * FROM (

SELECT HM, ID, NAME, DT,

RANK() OVER ( PARTITION BY HM ORDER BY DT DESC) RK

FROM ITP_TEST)

WHERE RK = 1

ORDER BY HM, DT DESC;

-- 12 rows selected.

DELETE FROM ITP_TEST

WHERE ROWID IN (

SELECT ROWID

FROM (

SELECT ROWID, RANK() OVER ( PARTITION BY HM ORDER BY DT DESC) RK

FROM ITP_TEST

)

WHERE RK > 1

);

-- 16 rows deleted.