数据库有很多其它列相同的行，如何删除掉？

今天碰到的一个挺有意思的笔试题
数据表table1建表语句如下代码，里面有很多pid+coin相同的行，请你编写sql删除这些重复的行

create table table1 (
  id BIGINT PRIMARY KEY,
  pid INT,
  coin INT,
  INDEX(pid,coin)
  );
  ALTER TABLE table1 DROP PRIMARY KEY;
ALTER TABLE table1 MODIFY COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY;
insert into table1(pid,coin) values(1,1),(1,1),(2,2),(2,2),(3,2),(3,2);

有很多pid + coin相同的行，如何删除掉？

delete t1 from table1 t1 INNER JOIN table1 t2 on t1.id < t2.id and t1.pid = t2.pid and t1.coin = t2.coin;

后面问ai，给了个更好的

delete from table1 where id not in (
SELECT * FROM (
    SELECT MIN(id) 
    FROM table1
    GROUP BY pid, coin
  ) tmp
  );