要删除tmp表中val值相同但是id不等于max(id)的记录,通常有两种方法:1)直接delete
DELETE tmp t
WHERE ID != (SELECT MAX (ID)
FROM tmp
WHERE val = t.val);2) 导出不重复纪录到临时表,清空原表,然后导回
CREATE TABLE t1 AS
SELECT *
FROM tmp t
WHERE ID = (SELECT MAX(ID) FROM tmp WHERE val=t.val);TRUNCATE TABLE tmp;INSERT INTO tmp
SELECT *
FROM t1;DROP TABLE t1;
DELETE tmp t
WHERE ID != (SELECT MAX (ID)
FROM tmp
WHERE val = t.val);2) 导出不重复纪录到临时表,清空原表,然后导回
CREATE TABLE t1 AS
SELECT *
FROM tmp t
WHERE ID = (SELECT MAX(ID) FROM tmp WHERE val=t.val);TRUNCATE TABLE tmp;INSERT INTO tmp
SELECT *
FROM t1;DROP TABLE t1;
如何优化Oracle表. ? 呢 ?