Mysql 查询表中某字段的重复值，删除重复值保留id最小的数据

1 查询重复值

SELECT * FROM Hb_Students WHERE studentId IN (SELECT studentId FROM Hb_Students  GROUP BY studentId HAVING count(studentId )>1 );

2 删除重复值

                
-- 创建临时表
CREATE TEMPORARY  TABLE Hb_Student_a (id int,studentNumber VARCHAR(100) );
    
-- 把重复数据放进临时表
INSERT Hb_Student_a SELECT id,studentNumber FROM Hb_Student 
WHERE studentNumber IN ( SELECT studentNumber FROM Hb_Student  GROUP BY studentNumber HAVING count( * ) > 1) 
AND id NOT IN (SELECT min(id) FROM Hb_Student  GROUP BY studentNumber HAVING count( * ) > 1 );
    
-- 删除重复数据
DELETE  a FROM Hb_Student AS a JOIN Hb_Student_a AS b ON a.id=b.id  WHERE a.id=b.id ;

 

转载于:https://www.cnblogs.com/ElvisZhongShao/p/9290586.html