尝试了以下几种方法,其中有种方法有点疑问,麻烦虾哥们帮帮忙看看我指的是记录的所有字段完全相同的情况思路是用distinct查询出唯一的那条记录 存到临时表里面 然后把表清空 把临时表的内容写进来select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp 输入的时候提示的错误信息是无效的字符
是不是临时表不是这么用的??
drop table tableName
select * into tableName from #Tmp
drop table #Tmp 输入的时候提示的错误信息是无效的字符
是不是临时表不是这么用的??
不适合Oracle
drop table tablename;
rename ttmp to tablename;
delete from tableName吧!
--以下是删除重复数据的3种方案
--3种方案都是最优化的
--object_name, object_id. 这2个字段用来判断重复--1.
delete
from x
where rowid in
(
select rd
from (
select rowid rd
,row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x
where rn > 1
)--2.
delete
from x
where rowid not in (
select max(rowid)
from x
group by object_name, object_id
)
--3.
create table tmp_x
AS
select x1.(字段列表..略)
from
(
select x.*, row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x1
where rn = 1;
truncate table x;insert into x
select *
from tmp_x; drop table tmp_x;
--根据要插入表记录的内容,定义一个结果集数据类型
TYPE TYPE_RECORD IS RECORD(
r_a table.a%type,
r_b table.b%type); --然后根据定义好的结果集定义一个表类型
TYPE TYPE_TABLE IS TABLE OF TYPE_RECORD INDEX BY BINARY_INTEGER;SELECT * BULK COLLECT
INTO T_TABLE
FROM (select distinct 字段 from tableName )
然后再写SQL语句INSERT INTO 把临时表T_TABLE里的数据插入回原表