把具有相同字段的纪录删除，只留下一条

一，多步骤实现

1. 将重复的记录记入temp1表:
   select [标志字段id],count(*) into temp1 from [表名]
   group by [标志字段id]
   having count(*)>1


2、将不重复的记录记入temp1表:
   insert temp1
   select [标志字段id],count(*) from [表名]
   group by [标志字段id]
   having count(*)=1


3、作一个包含所有不重复记录的表：
   select * into temp2 from [表名]
   where 标志字段id in(select 标志字段id from temp1)


4、删除重复表:
   delete [表名]


5、恢复表：
   insert [表名]
   select * from temp2


6、删除临时表:
   drop table temp1

   drop table temp2

二，单步骤实现

DELETE
FROM     temp t1
WHERE    EXISTS(
         SELECT   t2.主键
         FROM     temp t2
         WHERE    t2.主键 = t1.主键
         GROUP BY t2.主键
         HAVING   COUNT(*) > 1 )
     AND ROWID NOT IN(
         SELECT MIN(ROWID)
         FROM     temp t3

         WHERE t3.主键 = t1.主键 );

三，另一个方法

delete from test a
where rowid>(select min(rowid) from test b where a.name=b.name);