从SQL中删除重复的数据 [英] remove duplicate data from sql

问题描述

我有一个名为"data"的sql数据库和一个"disk"表，其中有5列

I have a sql database named "data" and a table "disk", where there are 5 columns

CREATE TABLE `disk` (
  `id` int(11) NOT NULL,
  `title` text COLLATE utf8_unicode_ci NOT NULL,
  `link` text COLLATE utf8_unicode_ci NOT NULL,
  `mag` text COLLATE utf8_unicode_ci NOT NULL,
  `size` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

"mag"列中有一些重复项.

the "mag" column has a some of the duplicates.

并且我想删除mag列相同的完整行.

and I want to delete the complete row where mag column is same.

注意:假设mag列有1,2,3,4,4,5....我想从中删除一个重复的4.表示我不想完全删除这两个4.必须保留一个"4".

note: let's say mag column has 1,2,3,4,4,5.... I want to delete a single 4 from it which is duplicate. means I don't want to completely delete both the 4. one "4" must be kept.

查询应该是什么样的?

推荐答案

您可以执行以下操作.
创建新表并保留随机行:

You can do the following..
Creating new table and keeping random row :

1. 将第一张表disk(唯一数据)复制到临时表disk2.

1. first copy table disk(unique data) to temp table disk2.

放置表disk.

将临时表disk2重命名为disk.

create table disk2 select * from disk group by d;

drop table disk;

rename table disk2 to disk;

注意:在这里，我们将group by与*一起使用是因为OP不在乎保留哪一行.

NOTE : Here we using group by with * because OP does not care which row to keep.

创建新表并使用最小或最大ID保留行: 在保持min或max id

/*copy data from disk to temp table disk2*/
create table disk2 select * from disk
    where id in (select min(id) from disk group by d);
/*drop table disk*/
drop table disk;
/*rename temp table to disk*/
rename table disk2 to disk;

更新:另一种方法
从现有表中删除重复项

UPDATE: Another way to do this
Deleting duplicates from existing table

    /*first create a dups table for duplicates*/
    create table dups select * from disk
        where id not in (select min(id) from disk group by d);
    /*now delete all rows which are present in dups table*/
    delete from disk where id in (select id from dups);
    /*now delete the dups table*/
    drop table dups;

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