我有一张这样的桌子
rowID |学生名称| rollNo |类 | | |1 | ABC | 10 |五2 | ABC | 10 |五3 | xyz | 11 |五…
试试这个吧
DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.rowID < t2.rowID AND t1.StudentName = t2.StudentName and t1.rollNo=t2.rollNo
这将删除所有重复的行,留下最高的行 rowID
rowID
我认为这样做你想要的:
select min(id), StudentName, rollNo, Class from t where class = 5 group by StudentName, rollNo, Class having count(*) > 1;
如果你真的想 删除 记录,然后你需要使用 delete :
delete
delete t from t left join (select studentname, rollno, class, min(id) as min_id, count(*) as cnt from t group by studentname, rollno, class ) tt on t.id = tt.minid and tt.cnt > 1 where tt.minid is null;