mysql - 如何从重复计数大于但保留一个的表中删除

我想从一个列数等于'REG'的表中删除行，该表的列数大于等于'UNR'的列数之一。但保留最后一个。

table
+------+------+--------+
| id   | name | status |
+------+------+--------+
|    1 | sa   | REG    |
|    2 | sam  | UNREG  |
|    3 | sa   | UNREG  |
|    4 | sam  | REG    |
|    5 | sak  | UNREG  |
|    6 | sak  | REG    |
|    7 | saa  | UNREG  |
|    8 | saa  | REG    |
|    9 | sam  | REG    |
|   10 | sa   | REG    |
+------+------+--------+


 DELETE n1 FROM names n1, names n2
 WHERE n1.id > n2.id
 AND count(case when n1.status = 'REG' then 1 end) > count(case when
 n2.status = 'UNREG' then 1 end);

此查询返回错误

ERROR 1111 (HY000): Invalid use of group function

在查询之后

 +------+------+--------+
 | id   | name | status |
 +------+------+--------+
 |    1 | sa   | REG    |
 |    2 | sam  | UNREG  |
 |    3 | sa   | UNREG  |
 |    4 | sam  | REG    |
 |    5 | sak  | UNREG  |
 |    6 | sak  | REG    |
 |    7 | saa  | UNREG  |
 |    8 | saa  | REG    |
 +------+------+--------+

最佳答案

下面是另一个简单的自连接表的解决方案，它避免了潜在的昂贵聚合。

delete n1
from names n1
inner join names n2
    on  n2.id < n1.id
    and n2.name = n1.name
    and n2.status = 'REG'
where n1.status = 'REG';

在记录中具有相同名称和较小的inner join的记录的自id过滤器。
Demo on DB Fiddle：
样本数据：
身份证|姓名|身份
-: | :--- | :-----
1 | sa |注册
2 |山姆|注销
3 | sa |注销
4 |山姆|雷格
5 | sak |注销
6 |萨克|注册
7 | saa |注销
8 | saa |注册
9 |山姆|雷格
10 | sa |注册
执行查询后的表内容：
身份证|姓名|身份
-: | :--- | :-----
1 | sa |注册
2 |山姆|注销
3 | sa |注销
4 |山姆|雷格
5 | sak |注销
6 |萨克|注册
7 | saa |注销
8 | saa |注册