Assume:
Create TableT (IDint not NULL Primary KeyAuto_increment,nameChar(Ten) not NULL, SexChar(2) not NULL) Engine=MyISAM;Insert intoTValues(NULL,'Tom','male'),(NULL,'Jack','male'),(NULL,'Xiao Wen','male'),(NULL,'Xiao Wen','female'),(NULL,'Tom','male'),(NULL,'Xiao Zhang','male'),(NULL,'Xiao Zhao','female'),(NULL,'Tom','male'),(NULL,'Jack','male'),(NULL,'Xiao Zhao','female');
Delete duplicate field SQL code
DeleteT asa fromT asA, (Select * fromTGroup byName having Count(1)>1) asbwhereA.name=B.name anda.ID>b.ID;
Detailed explanation
1. First remove all duplicate first fields
Select * from Group by having Count (1) > 1
2, and the original table composition comparison chart
Select * from as A, (Select*fromgroupby haveCount( 1) > 1 as b where a.name=b.name;
3. Remove all fields other than the ID number of the first field
Select * from as A, (Select*fromgroupby haveCount( 1) > 1 as b where a.name=b.nameand> b.id;
4, to delete
DeleteT asa fromT asA, (Select * fromTGroup byName having Count(1)>1) asbwhereA.name=B.name anda.ID>b.ID;
MySQL removes duplicate fields from a column in a table