有这么一个表,他可能但不一定会出现如下情况
id name pass x
...
1 name1 123 1
...有n个name1重复的
n name1 123 0
n+1 name2 321 1
n+2 name3 456 1
...
就是说,name这个字段有几个重复的,而x这个字段又不一样。
我现在要写这么一条SQL语句实现这么一个功能,
就是,在这些重复的记录里面,我只保留最后一条,前面的删掉。
怎么写呢?
SQL Server版:
delete t1 where [name] in (
select [name] from t1 group by [name] having count([name])>1)
and [id] not in
(select max([id]) from t1 group by [name] having count([name])>1)
MySQL 版:
由于MySQL不能对同一个表同时查询和删除,所以只能通过建立一个临时表的方法来解决
create table tmp_table
as select id from stu
where name in
(select name from stu group by name having count(name) > 1)
and id not in (select id from stu group by name having count(name) > 1);
delete from stu where id in (select
id from tmp_table);
drop table tmp_table;