delphi mysql 删除_delphi删除数据库重复记录的4种方法

方法一. 使用Group子查询查出重复的条目 (这里用max得到大的ID)

DELETE FROM [user] WHERE ID NOT IN

(SELECT MAX(ID) FROM [user] GROUP BY [name])

方法二. 使用自连接

delete from table where ID = (

select ID from table t1 , table t2 where t1.name = t2.name and

t1.ID > t2.ID )

方法三. 使用重新插入 Ignore dup key

--第一板斧——建立一张具有相同结构的临时表

--建立临时表

select * into tempCard from cmc_contactCard where 1=2

--第二板斧——为该表加上索引，并使其忽略重复的值

--在临时表上建立忽略重复值的索引

CREATE UNIQUE

INDEX Index_Name ON tempCard (Enterprise_Name, Contact_Name)

WITH

IGNORE_DUP_KEY

--第三板斧——拷贝产品信息到临时表

--插入源表数据到临时表

insert into tempCard select * from cmc_contactCard

--第四板斧——将新的数据导入原表

--清空源表记录

truncate table cmc_contactCard

--回写所有消掉重复值的记录

insert into cmc_contactCard select * from tempCard

--删除临时表

drop table tempCard

方法四: 使用游标

--创建表并填充数据

create table b_dist(id int,name varchar(20), dept

varchar(20))

insert into b_dist values(1,'abc','sales')

insert into b_dist values(2,'abc','sales')

insert into b_dist values(3,'abc','market')

insert into b_dist values(4,'abc','sales')

insert into b_dist values(5,'abc','infos')

insert into b_dist values(6,'abc','market')

insert into b_dist values(7,'abc','backup')

--使用游标逐个比较, 把关键的字段要排序

declare @id int

declare @name varchar(20)

declare @dept varchar(20)

declare @prevname varchar(20)

declare @prevdept varchar(20)

declare cur cursor for select id, name, dept from b_dist order by

name, dept

open cur

fetch next from cur into @id, @name, @dept

while @@FETCH_STATUS = 0

begin

if (@dept=@prevdept) and (@name=@prevname)

delete from b_dist where id=@id

select @prevname=@name, @prevdept=@dept

fetch next from cur into @id, @name, @dept

end

close cur

deallocate cur