在mysql中删除计数大于1的重复行 [英] Delete duplicate rows having count greater than 1 in mysql

问题描述

需要有关MySQL查询的帮助，以删除userID计数大于1且acl值= 4的行.

Need some help on a MySQL query to delete the rows where the userID count is greater than one and has an acl value=4.

我尝试作为选择查询的内容:

What I've tried as the select query:

DELETE FROM `users_acl` 
WHERE userID IN(
    SELECT * FROM `users_acl` 
    WHERE `acl` BETWEEN 2 AND 4 
    GROUP BY userID 
    HAVING COUNT(userID) > 1
);

但是找不到在同一查询中删除行的正确方法.

But can't find a proper way to delete the rows in the same query.

表结构

+--------+-----+---------+
| userID | acl | deleted |
+--------+-----+---------+
|     27 |   2 |       0 |
|     28 |   2 |       0 |
|     31 |   2 |       0 |
|     42 |   2 |       0 |
|     42 |   4 |       0 |
|     45 |   1 |       0 |
|     51 |   1 |       0 |
|     54 |   1 |       0 |
|     63 |   2 |       0 |
|     63 |   4 |       0 |
|     64 |   1 |       0 |
|     69 |   2 |       0 |
|     73 |   2 |       0 |
|     73 |   4 |       0 |
|     76 |   1 |       0 |
|     77 |   2 |       0 |
|     77 |   4 |       0 |
+--------+-----+---------+

推荐答案

在MySQL中，您无法同时从要删除的表中进行选择.但是使用临时表可以解决此问题

In MySQL you can't select from a table you are deleting from at the same time. But with a temp table you can overcome this problem

DELETE FROM `users_acl` 
WHERE userID IN
(
    SELECT * FROM
    (
       SELECT userID
       FROM `users_acl`
       GROUP BY userID 
       HAVING COUNT(userID) > 1
       AND SUM(`acl` = 4) > 0
   ) tmp
);

或使用join代替

DELETE u
FROM `users_acl` u
JOIN 
(
    SELECT userID
    FROM `users_acl`
    GROUP BY userID 
    HAVING COUNT(userID) > 1
    AND SUM(`acl` = 4) > 0
) tmp on tmp.userID = u.userID

这篇关于在mysql中删除计数大于1的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！