mysql - SQL - 从表 X 中删除所有重复条目

Question

我尝试从表 fe_users 中获取所有多余 UID 的列表，然后运行我的 DELETE 命令删除它们。

例如我有这张表 (fe_users):

uid | pid | username
--------------
1   | 100 |  hans
2   | 100 |  karl
3   | 100 |  franz
4   | 100 |  karl
5   | 100 |  hans

这就是我选择所有重复项的方式:

SELECT * FROM fe_users
WHERE uid NOT IN (
    SELECT uid  
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)
AND username IN (
    SELECT username 
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)

返回:

uid | pid | username
--------------------
5   | 100 |  hans
4   | 100 |  karl

现在我必须删除它们。

---

尝试 #1:

(提示:刚刚将上面查询中的SELECT替换为DELETE)

DELETE * FROM fe_users
WHERE uid NOT IN (
    SELECT uid  
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)
AND username IN (
    SELECT username 
    FROM fe_users
    WHERE pid=100
    GROUP BY username
    HAVING COUNT(username) > 1
)

响应:

You can't specify target table 'fe_users' for update in FROM clause

---

尝试#2

所以我尝试再使用一个选择，就像 this answer 中建议的那样.

DELETE FROM fe_users
WHERE uid NOT IN (
    SELECT uid (
        SELECT uid
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        )
    ) AS uid
AND username IN (
    SELECT username (
        SELECT username 
        FROM fe_users
        WHERE pid=100
        GROUP BY username
        HAVING COUNT(username) > 1
        )
    ) AS username;

响应:

Syntax error near 'SELECT uid FROM fe_users WHERE pid=0 GROUP BY usernam' at line 4

我不知道我该怎么做，我想知道 MySQL 开发人员在实现这种毫无意义的限制时是怎么想的。

我必须使用 PHP 删除它吗？

Answer 1

您的查询似乎比必要的更复杂。我会去:

delete u
    from fe_users u join
         fe_users u2
         on u.pid = u2.pid and u.username = u2.username and
            u2.uid > u.uid
    where u.pid = 100;

这将删除所有具有相同 pid 和 username 且具有更大 uid 的行。您可以通过将 delete u 更改为 select u.* 来获取列表。