删除重复数据mysql

wlzqhblo 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(307)

如果我们使用

SELECT MAX(id), COUNT(id) AS count 
FROM user_status 
GROUP BY user_id, user_type 
HAVING COUNT(*) > 1

我们得到了重复的数据
现在，当我试图通过删除插入它

DELETE 
FROM user_status 
WHERE id IN (SELECT MAX(id), COUNT(id) AS count 
             FROM user_status 
             GROUP BY user_id, user_type 
             HAVING COUNT(*) > 1
            )

我犯了以下错误

Operand should contain 1 column

我怎样才能解决这个问题？短暂性脑缺血发作

sql mysql

来源：https://stackoverflow.com/questions/52603410/deleting-duplicate-data-mysql

2条答案

按热度按时间

zte4gxcn1#

很明显，您选择的是 subquery 这是不允许的 IN 但仍然 mysql 在这样的from of查询中显示了一些错误，因此请将查询替换为join，如下所示

DELETE us 
FROM user_status us
INNER JOIN (SELECT MAX(id) id 
            FROM user_status 
            GROUP BY user_id, user_type HAVING COUNT(*) > 1) t ON t.id = us.id

赞(0）回复(0）举报 2021-06-21

7cjasjjr2#

怎么样

DELETE user_status
FROM user_status LEFT JOIN (
    SELECT MAX(user_id) maxId
    FROM user_status
    GROUP BY user_type
) a ON maxId = user_id
WHERE maxId IS NULL;

在你的代码里 where col1 in (select col1, col2) 列计数根本不匹配。如果引用每个选定的列，原则上是这样的：

DELETE FROM user_status
WHERE (user_id, user_type) NOT IN (
    SELECT MAX(user_id) maxId, user_type
    FROM (select * from user_status) a
GROUP BY user_type);

赞(0）回复(0）举报 2021-06-20

我来回答

删除重复数据mysql

2条答案

相关问题

热门标签

最新问答