如何使用mysql去除表中重复的字段

发布时间:2023年12月17日

简介:

此处的建表题目来自我们的也门哥Maged,非常感谢他出的这些测试题目,让我能够独立思考,反复试去找到cw2的正确做法。

数据库准备:

害怕被好homi被刺然后被?academic warning 所以浅浅打个码。

  1. 创建好这张表后我们我们可以显示创建一个自增主键(如若不创建mysql会隐式创建一个主键),方便我们后续操作。
  2. ALTER TABLE roles
        ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

问题解决步骤:

此时我们可以使用?

SELECT RoleID, COUNT(*) as count
FROM roles
GROUP BY RoleID
HAVING count > 1;

来查询重复的行数据。

此时我们会引出一个问题:我们查找到了重复数据,如何删除呢?

这时就可以使用上我们创建的主键id,由于该表roleid不满足唯一性,所以它不可能是主键id,所以我们可以使用一个聚合函数 min(),来确定最小id的重复数据行。

select
    min(id)
from roles
group by RoleID
having count(*) > 1;

查询出数据后我们只需要结合上面两次查询到的数据,使用delete语句,删除查询到的重复数据即可。但是我们不可以直接进行删除如:

delete from roles where RoleID in (
        select RoleID
        from roles
        group by RoleID
        having count(*) > 1
    )
and id not in(
        select min(id) as min_id
        from roles
        group by RoleID
        having count(*) > 1
    )

这样会报错:You can't specify target table 'user_info' for update in FROM clause

这是因为 MySQL 数据库不允许在?DELETE?语句的子查询中直接引用目标表,而通过在子查询外部再包裹一层查询,可以绕过这个限制。可以使用额外的子查询层级,将原始子查询的结果作为临时表,然后在外部的?DELETE?语句中引用这个临时表。

delete from roles where RoleID in (
    select X.* from (
        select RoleID
        from roles
        group by RoleID
        having count(*) > 1
        )as X
    )
and id not in(
    select min_id from (
        select min(id) as min_id
        from roles
        group by RoleID
        having count(*) > 1
        ) as y
    )

如此一来即可删除掉重复的数据。

然后使用

ALTER TABLE roles
    drop column id;

删除掉id字段,并且使用

ALTER TABLE roles
ADD CONSTRAINT pk_roles PRIMARY KEY (RoleID);

即可将RoleID设置为roles表主键id,并且其会自动创建索引。

文章来源:https://blog.csdn.net/weixin_74783792/article/details/135022115
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。