表样
user
---------------------
staff_no staff_name reg_date
001 张三 2008-8-21 09:00:00
001 李四 2008-8-01 15:00:00
001 王五 2008-7-21 09:00:00
要把reg_date离sysdate最近那条保留下来,也就是说把staff_no为重复的记录’李四‘和‘王五’两行给予删除!
user
---------------------
staff_no staff_name reg_date
001 张三 2008-8-21 09:00:00
001 李四 2008-8-01 15:00:00
001 王五 2008-7-21 09:00:00
要把reg_date离sysdate最近那条保留下来,也就是说把staff_no为重复的记录’李四‘和‘王五’两行给予删除!
DELETE FROM USER
WHERE ROWID IN (SELECT RD
FROM (SELECT ROWID RD,
ROW_NUMBER() OVER(PARTITION BY STAFF_NO ORDER BY REG_DATE DESC) RN
FROM USER)
WHERE RN <> 1);
where staff_name <>
(select staff_name
from (select user.*, abs(reg_date - sysdate) mi from user order by mi)
where rownum = 1)
测试过了。
WHERE (staff_no,reg_date) not in (select staff_no,max(reg_Date) from user group by staff_no)
DELETE FROM USER
WHERE ROWID IN(
SELECT RI
FROM (
SELECT ROWID RI,
ROW_NUMBER() OVER(ORDER BY ABS(REG_DATE -SYSDATE) DESC) AS RN
FROM USER U
)
WHERE RN >= 2
);
WHERE ROWID IN (SELECT ROWID
FROM USER A WHERE NOT EXISTS(
SELECT * USER B WHERE ABS(B.REG_DATE -SYSDATE)>ABS(A.REG_DATE -SYSDATE)
)
);