DELETE FROM TAB WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TAB GROUP BY PK_COL) PK_COL為你需要更改的表中的主鍵欄位或唯一鍵欄位
delete from table WHERE ROWID IN (SELECT row_id FROM (SELECT ROWID row_id, ROW_NUMBER() OVER(PARTITION BY id ORDER BY ROWID) rn FROM table) WHERE rn > 1)
类似如下,分组先 delete from emply where id not in (select max(id) from Sun_Test group by name)
会不会一个最呆的方法,CREATE TABLE A SELECT DISTINCT * FROM B ,然后把你的B表DELETE了,再INSETT INTO B SELECT * FROM A
delete from table WHERE ROWID IN (SELECT row_id FROM (SELECT ROWID row_id, ROW_NUMBER() OVER(PARTITION BY col1,col2,col3,....ORDER BY ROWID) rn FROM table) WHERE rn > 1)
但少了as CREATE TABLE A AS SELECT DISTINCT * FROM B INSETT INTO B SELECT * FROM A
试试我的: SQL> select * from test1; ID ---------- 12 12 1 1SQL> delete from test1 a 2 where rowid<(select max(rowid) from test1 b where a.id=b.id);已删除2行。SQL> select * from test1; ID ---------- 12 1SQL>
如果表中有多个字段: a.col1=b.col1 and a.col2=b.ciol2.......
delete from tableName A where rowid<(select max(rowid) from tableName B where A.id=B.id);
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TAB GROUP BY PK_COL)
PK_COL為你需要更改的表中的主鍵欄位或唯一鍵欄位
WHERE ROWID IN (SELECT row_id
FROM (SELECT ROWID row_id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY ROWID) rn
FROM table)
WHERE rn > 1)
delete from emply
where id not in
(select max(id)
from Sun_Test
group by name)
WHERE ROWID IN (SELECT row_id
FROM (SELECT ROWID row_id,
ROW_NUMBER() OVER(PARTITION BY col1,col2,col3,....ORDER BY ROWID) rn
FROM table)
WHERE rn > 1)
CREATE TABLE A AS SELECT DISTINCT * FROM B
INSETT INTO B SELECT * FROM A
SQL> select * from test1; ID
----------
12
12
1
1SQL> delete from test1 a
2 where rowid<(select max(rowid) from test1 b where a.id=b.id);已删除2行。SQL> select * from test1; ID
----------
12
1SQL>
a.col1=b.col1 and a.col2=b.ciol2.......
where
rowid<(select max(rowid) from tableName B where A.id=B.id);