SQL Oracle删除重复记录

    技术2022-05-12  10

    1.Oracle删除重复记录.

    删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录.

    delete from people where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

    删除表中多余的重复记录(多个字段),只留有rowid最小的记录delete from vitae awhere (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

    ================================

    此方法可以适用于sql ,oracle

    declare @max integer,@id integer

    declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1

    open cur_rows

    fetch cur_rows into @id,@max

    while @@fetch_status=0

    begin

    select @max = @max -1

    set rowcount @max

    delete from 表名 where 主字段 = @id

    /*

    DECLARE @count INT SELECT @count = COUNT(*) FROM [table1] WHERE [column1] = 1 DELETE TOP (@count-1) FROM [table1] WHERE [column1] = 1  这个top后面一定要有括号

    */

    fetch cur_rows into @id,@max

    end

    close cur_rows

    set rowcount 0

     =======================================

    select distinct * into #Tmp from tableName

    drop table tableName

    select * into tableName from #Tmp

    drop table #Tmp

     

    select identity(int,1,1) as autoID, * into #Tmp from tableName

    select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

    select * from #Tmp where autoID in(select autoID from #tmp2)

    =======================================

     select identity(int,1,1) as id ,name,state into #tempTable from adelete from adelete from #tempTablewhere id not in( select min(id) from #tempTable group by name)

    insert into a( name,state)select name,state from #tempTable

    drop table #tempTable


    最新回复(0)