一、在mysql表有多条完全重复的记录,也无主键
1、给表增加一个自增长的列作为主键,这样的话,完全重复的数据就有了不一样的地方
alter table 表名 add column id int PRIMARY KEY AUTO_INCREMENT;`
2、删除重复的列中ID较小的那一个,保留较大的id
delete t1 from test t1 inner join test t2 where t1.id < t2.id AND t1.sfzh=t2.sfzh
3、删除掉新增的主键ID
ALTER table `表名` DROP column id;
4、把原来表中的一列设置为主键
ALTER table `表名` add PRIMARY KEY (user_role_id)
二、MSSQL
--查找sfzh重复的数据 SELECT * from Sheet1 where sfzh in(SELECT sfzh from Sheet1 GROUP BY sfzh HAVING count(*)>1) --删除重复记录,只保留ID号最大的一条 delete from Sheet1 where sfzh in (select sfzh from Sheet1 group by sfzh having count(sfzh) > 1) and id not in (select max(id) from Sheet1 group by sfzh having count(sfzh)>1) --查看全部记录 SELECT * from Sheet1