oracle中刪除重復(fù)記錄(完整版)
【正文】 建唯一索引時，系統(tǒng)提示 ora01452 ：不能創(chuàng)建唯一索引，發(fā)現(xiàn)重復(fù)記錄。 c1 c10 c20 1 2 dsf 2 3 che 3 4 dff：(1).適用于有大量重復(fù)記錄的情況(在c1,c10和c20列上建有索引的時候，用以下語句效率會非常高)：sqldelete cz where (c1,c10,c20) in (select c1,c10,c20 from cz group by c1,c10,c20 having count(*)1) and rowid not in(select min(rowid) from cz group by c1,c10,c20 having count(*)1)。 (再將臨時表test里的內(nèi)容反插回來）(4).適用于有大量重復(fù)記錄的情況(exception into 子句法)：采用alter table 命令中的 exception into 子句也能確定出庫表中重復(fù)的記錄。row_idaaahd/aaiaaaadsaaaaaahd/aaiaaaadsaabaaahd/aaiaaaadsaacaaahd/aaiaaaadsaafaaahd/aaiaaaadsaahaaahd/aaiaaaadsaaiaaahd/aaiaaaadsaagaaahd/aaiaaaadsaadaaahd/aaiaaaadsaaeaaahd/aaiaaaadsaajaaahd/aaiaaaadsaakaaahd/aaiaaaadsaalaaahd/aaiaaaadsaamaaahd/aaiaaaadsaanaaahd/aaiaaaadsaaoaaahd/aaiaaaadsaap16 rows selected.sqldelete from cz where rowid in ( select row_id from exceptions)。3 rows created.sqlselect *from cz。創(chuàng)建該表的 sql 腳本文件為 。(2).適用于有少量重復(fù)記錄的情況(注意，對于有大量重復(fù)記錄的情況，用以下語句效率會非常低)：sqldelete from cz a where !=(select max(rowi
環(huán)評公示相關(guān)推薦
鄂ICP備17016276號-1