MySQL 根据主键和一列删除除一个值之外的所有值

Question

我的 table

 id   userid    emailid
 ---+---------+------------
 1  |  1      |  a@a.com
 2  |  1      |  b@b.com
 3  |  123    |  a@a.com
 4  |  123    |  b@b.com
 5  |  123    |  c@c.com
 6  |  123    |  c@c.com

期望结果

id    userid    emailid
 ---+---------+------------
 1  |  1      |  a@a.com
 2  |  1      |  b@b.com
 3  |  123    |  a@a.com
 4  |  123    |  b@b.com
 5  |  123    |  c@c.com

但我越来越

id    userid    emailid
 ---+---------+------------
 1  |  1      |  a@a.com
 2  |  1      |  b@b.com
 6  |  123    |  c@c.com

我尝试过(我是 SQL 新手)

DELETE FROM table 
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id) 
FROM table n GROUP BY n.emailid) x)

Answer 1

试试这个。

查询

DELETE FROM table_name 
WHERE id NOT IN
(
   SELECT MIN(id)
   FROM table_name
   GROUP BY userid,emailid
);