要求:
表a有两个字段id,name。id是主键,子增长;name有重复。使用单一SQL,除去多余的重复记录。如name有4个重复的值,删除3个,保留一个即可。
创建a表:
CREATE TABLE `a` (
`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL
)
SQL删除语句:
delete from a
where a.id in
(
select * from
(
select distinct t.id
from a, a as t
where a.name=t.name and t.id>a.id
) as tmp
)
注意:一定要有这个临时表。
改进:
delete from a
where id not in
(
select min(id)
from a
group by name
)
注意:应该给name字段加上索引。