mysql - 如何删除mysql中的唯一记录？

Question

我有一张这样的桌子

rowID | StudentName | rollNo | Class
      |             |        |
1     | ABC         |10      |  5   
2     | ABC         |10      |  5
3     | xyz         |11      |  5
4     | asd         |12      |  5

如何删除unique类所在的所有记录5

所以我有剩余的桌子

rowID | StudentName | rollNo | Class
      |             |        |
1     | ABC         |10      |  5  

标签： mysqlsql

Answer 1

我认为这可以满足您的要求：

select min(id), StudentName, rollNo, Class
from t
where class = 5
group by StudentName, rollNo, Class
having count(*) > 1;

如果你真的想删除记录，那么你需要使用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;