精华内容
下载资源
问答
  • MYSQL用一条SQL语句删除重复记录MYSQL不支持如下语句:delete from t_user where id in(select max(id) as id from t_user group by username );MYSQL用以下词句就可以:delete t_user from t_user , (select id ...

    MYSQL用一条SQL语句删除重复记录

    MYSQL不支持如下语句:

    delete from t_user where id in(select max(id) as id from t_user group by username );

    MYSQL用以下词句就可以:

    delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;

    例子如下:

    drop table t_user;

    create table t_user(

    id        int(5) not null auto_increment,

    username varchar(10),

    age       int(3),

    primary key(id)

    );

    insert into t_user(username,age) values('aaa',20);

    insert into t_user(username,age) values('aaa',20);

    insert into t_user(username,age) values('bbb',20);

    insert into t_user(username,age) values('bbb',20);

    insert into t_user(username,age) values('ccc',20);

    insert into t_user(username,age) values('ccc',20);

    insert into t_user(username,age) values('ddd',20);

    insert into t_user(username,age) values('ddd',20);

    mysql> select * from t_user;

    +----+----------+------+

    | id | username | age |

    +----+----------+------+

    | 1 | aaa      |   20 |

    | 2 | aaa      |   20 |

    | 3 | bbb      |   20 |

    | 4 | bbb      |   20 |

    | 5 | ccc      |   20 |

    | 6 | ccc      |   20 |

    | 7 | ddd      |   20 |

    | 8 | ddd      |   20 |

    +----+----------+------+

    mysql> delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;

    Query OK, 4 rows affected (0.05 sec)

    mysql> select * from t_user;

    +----+----------+------+

    | id | username | age |

    +----+----------+------+

    | 2 | aaa      |   20 |

    | 4 | bbb      |   20 |

    | 6 | ccc      |   20 |

    | 8 | ddd      |   20 |

    +----+----------+------+

    展开全文
  • sql中我们经常会碰到有重复的一些数据,下面我来介绍在mysql中删除重复记录的多种方法,有需要的朋友可参考参考。删除重复记录方法一:1. 新建一个临时表代码如下create table tmp as select * from youtable ...

    在sql中我们经常会碰到有重复的一些数据,下面我来介绍在mysql中删除重复记录的多种方法,有需要的朋友可参考参考。

    删除重复记录方法一:

    1. 新建一个临时表

    代码如下

    create table tmp as select * from youtable group by name(name为不希望有重复的列)

    2. 删除原来的表

    代码如下

    drop table youtable

    3. 重命名表

    代码如下

    alter table tmp rename youtable

    但是这个方法有个问题,由临时表转变过来的最终表,其表结构会和原来的不一致,需要手工更改。这个问题,待解决。

    删除重复记录方法二:

    1. 新建一个临时表

    代码如下

    CREATE TABLE tmp AS SELECT * FROM youtable GROUP BY name(name为不希望有重复的列)

    2. 清空原来的表

    代码如下

    TRUNCATE TABLE youtable

    3. 把临时表插入到youtable

    代码如下

    INSERT INTO tablename SELECT * FROM temp

    4. 删除临时表

    代码如下

    DROP TABLE temp

    删除重复记录方法三:

    代码如下

    delete table where ID not in(select min(ID) from table group by name(name:重复的字段))

    删除重复记录方法四:

    具体实现如下:

    代码如下

    Table Create Table

    ------------ --------------------------------------------------------

    users_groups CREATE TABLE `users_groups` (

    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `uid` int(11) NOT NULL,

    `gid` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

    users_groups.txt内容:

    1,11,502

    2,107,502

    3,100,503

    4,110,501

    5,112,501

    6,104,502

    7,100,502

    8,100,501

    9,102,501

    10,104,502

    11,100,502

    12,100,501

    13,102,501

    14,110,501

    mysql> load data infile 'c:\users_groups.txt' into table users_groups fields

    terminated by ',' lines terminated by 'n';

    Query OK, 14 rows affected (0.05 sec)

    Records: 14 Deleted: 0 Skipped: 0 Warnings: 0

    mysql> select * from users_groups;

    query result(14 records)

    id uid gid

    1 11 502

    2 107 502

    3 100 503

    4 110 501

    5 112 501

    6 104 502

    7 100 502

    8 100 501

    9 102 501

    10 104 502

    11 100 502

    12 100 501

    13 102 501

    14 110 501

    14 rows in set (0.00 sec)

    根据一位兄弟的建议修改。

    代码如下

    mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) >= 1;

    Query OK, 7 rows affected (0.11 sec)

    Records: 7 Duplicates: 0 Warnings: 0

    mysql> truncate table users_groups;

    Query OK, 14 rows affected (0.03 sec)

    mysql> insert into users_groups select * from tmp_wrap;

    Query OK, 7 rows affected (0.03 sec)

    Records: 7 Duplicates: 0 Warnings: 0

    mysql> select * from users_groups;

    query result(7 records)

    id uid gid

    1 11 502

    2 107 502

    3 100 503

    4 110 501

    5 112 501

    6 104 502

    9 102 501

    mysql> drop table tmp_wrap;

    Query OK, 0 rows affected (0.05 sec)

    2、还有一个很精简的办法。

    查找重复的,并且除掉最小的那个。

    代码如下

    delete users_groups as a from users_groups as a,

    (

    select *,min(id) from users_groups group by uid having count(1) > 1

    ) as b

    where a.uid = b.uid and a.id > b.id;

    (7 row(s)affected)

    (0 ms taken)

    query result(7 records)

    id uid gid

    1 11 502

    2 107 502

    3 100 503

    4 110 501

    5 112 501

    6 104 502

    9 102 501

    3、现在来看一下这两个办法的效率。

    运行一下以下SQL 语句

    代码如下

    create index f_uid on users_groups(uid);

    explain select * from users_groups group by uid having count(1) > 1 union all

    select * from users_groups group by uid having count(1) = 1;

    explain select * from users_groups as a,

    (

    select *,min(id) from users_groups group by uid having count(1) > 1

    ) as b

    where a.uid = b.uid and a.id > b.id;

    query result(3 records)

    id select_type table type possible_keys key key_len ref rows Extra

    1 PRIMARY users_groups index (NULL) f_uid 4 (NULL) 14

    2 UNION users_groups index (NULL) f_uid 4 (NULL) 14

    (NULL) UNION RESULT ALL (NULL) (NULL) (NULL) (NULL) (NULL)

    query result(3 records)

    id select_type table type possible_keys key key_len ref rows Extra

    1 PRIMARY ALL (NULL) (NULL) (NULL) (NULL) 4

    1 PRIMARY a ref PRIMARY,f_uid f_uid 4 b.uid 1 Using where

    2 DERIVED users_groups index (NULL) f_uid 4 (NULL) 14

    很明显的第二个比第一个扫描的函数要少。

    当没有创建表或创建索引权限的时候

    创建一个新表,然后将原表中不重复的数据插入新表:

    代码如下

    mysql> create table demo_new as select * from demo group by site;

    Query OK, 3 rows affected (0.19 sec)

    Records: 3 Duplicates: 0 Warnings: 0

    mysql> show tables;

    +----------------+

    | Tables_in_test |

    +----------------+

    | demo |

    | demo_new |

    +----------------+

    2 rows in set (0.00 sec)

    mysql> select * from demo order by id;

    +----+------------------------+

    | id | site |

    +----+------------------------+

    | 1 | http://www.hzhuti.com |

    | 2 | http://111cn.net |

    | 3 | http://www.111cn.net |

    | 4 | http://www.hzhuti.com |

    | 5 | http://www.111cn.net |

    +----+------------------------+

    5 rows in set (0.00 sec)

    mysql> select * from demo_new order by id;

    +----+------------------------+

    | id | site |

    +----+------------------------+

    | 1 | http://www.hzhuti.com |

    | 2 | http://111cn.net |

    | 3 | http://www.111cn.net |

    +----+------------------------+

    3 rows in set (0.00 sec)

    然后将原表备份,将新表重命名为当前表:

    代码如下

    mysql> rename table demo to demo_old, demo_new to demo;

    Query OK, 0 rows affected (0.04 sec)

    mysql> show tables;

    +----------------+

    | Tables_in_test |

    +----------------+

    | demo |

    | demo_old |

    +----------------+

    2 rows in set (0.00 sec)

    mysql> select * from demo order by id;

    +----+------------------------+

    | id | site |

    +----+------------------------+

    | 1 | http://www.hzhuti.com |

    | 2 | http://111cn.net |

    | 3 | http://www.111cn.net |

    +----+------------------------+

    3 rows in set (0.00 sec)

    本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

    本文系统来源:php中文网

    TAG标签:删除重复记录

    展开全文
  • Oracle查询并删除重复记录SQL语句2010-07-20 00:00出处:中国IT实验室作者:佚名【我要评论】 [导读]查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断。企业软件热点文章Java调用Dll存在指针或...

    Oracle查询并删除重复记录的SQL语句

    2010-07-20 00:00出处:中国IT实验室作者:佚名【我要评论】 [导读]查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断。

    企业软件热点文章

    Java调用Dll存在指针或变参的解决方法 Oracle中非默认方式创建外键的使用

    查询及删除重复记录的SQL语句

    1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

    select * from people

    where peopleId in (select peopleId from people group by peopleId having count(peopleId) >1)

    2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有 rowid最小的记录

    delete from people

    where peopleId in (select peopleId from people group by peopleId having count(peopleId) >1)

    and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

    注:rowid为oracle自带不用该.....

    3、查找表中多余的重复记录(多个字段)

    select * from vitae a

    where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) >1)

    4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

    delete from vitae a

    where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) >1)

    and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

    5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

    select * from vitae a

    where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) >1)

    and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

    (二)

    比方说

    在A表中存在一个字段“name”,

    而且不同记录之间的“name”值有可能会相同,

    现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;

    Select Name,Count(*) From A Group By Name Having Count(*) >1

    如果还查性别也相同大则如下:

    Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) >1

    (三)

    方法一

    declare @max integer,@id integer

    declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1

    open cur_rows

    fetch cur_rows into @id,@max

    while @@fetch_status=0

    begin

    select @max = @max -1

    set rowcount @max

    delete from 表名 where 主字段 = @id

    fetch cur_rows into @id,@max

    end

    close cur_rows

    set rowcount 0 方法二

    "重复记录"有两个意义上的重复记录,一是完全重复的记录,也即所

    展开全文
  • 如果要删除手机(mobilePhone),电话(officePhone),邮件(email)同时都相同的数据,以前一直使用这条语句进行去重:delete from 表 where id not in (select max(id) from 表 group by mobilePhone,officePhone,em...

    海量数据(百万以上),其中有些全部字段都相同,有些部分字段相同,怎样高效去除重复?

    如果要删除手机(mobilePhone),电话(officePhone),邮件(email)同时都相同的数据,以前一直使用这条语句进行去重:

    delete from 表 where id not in  (select max(id) from 表 group by mobilePhone,officePhone,email )  or delete from 表 where id not in   (select min(id) from 表 group by mobilePhone,officePhone,email ) delete from 表 where id not in (select max(id) from 表 group by mobilePhone,officePhone,email ) or delete from 表 where id not in (select min(id) from 表 group by mobilePhone,officePhone,email )

    其中下面这条会稍快些。上面这条数据对于100万以内的数据效率还可以,重复数1/5的情况下几分钟到几十分钟不等,

    但是如果数据量达到300万以上,效率骤降,如果重复数据再多点的话,常常会几十小时跑不完,有时候会锁表跑一夜都跑不完。

    无奈只得重新寻找新的可行方法,今天终于有所收获:

    //查询出唯一数据的ID,并把他们导入临时表tmp中  select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email   //查询出去重后的数据并插入finally表中  insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp) //查询出唯一数据的ID,并把他们导入临时表tmp中 select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email //查询出去重后的数据并插入finally表中 insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp)

    效率对比:用delete方法对500万数据去重(1/2重复)约4小时。4小时,很长的时间。

    用临时表插入对500万数据去重(1/2重复)不到10分钟。

    其实用删除方式是比较慢的,可能是边找边删除的原因吧,而使用临时表,可以将没有重复的数据ID选出来放在临时表里,再将表的信息按临时表的选择出来的ID,将它们找出来插入到新的表,然后将原表删除,这样就可以快速去重啦。

    SQL语句去掉重复记录,获取重复记录

    按照某几个字段名称查找表中存在这几个字段的重复数据并按照插入的时间先后进行删除,条件取决于order by 和row_num。

    方法一按照多条件重复处理:

    delete tmp from(  select row_num = row_number() over(partition by 字段,字段 order by 时间 desc)   from 表 where 时间> getdate()-1   ) tmp   where row_num > 1 delete tmp from( select row_num = row_number() over(partition by 字段,字段 order by 时间 desc) from 表 where 时间> getdate()-1 ) tmp where row_num > 1

    方法二按照单一条件进行去重:

    delete from 表 where 主键ID not in(  select max(主键ID) from 表 group by 需要去重的字段 having count(需要去重的字段)>=1   ) delete from 表 where 主键ID not in( select max(主键ID) from 表 group by 需要去重的字段 having count(需要去重的字段)>=1 )

    展开全文
  • 我们经常会碰到需要删除数据表中重复记录,下面我来总结了几种能删除重复记录并助相对来讲效率是非常不错的,有需要了解的朋友可进入参考。创建一个表用来存放,要删除的记录的id信息:代码如下复制代码CREATE TABLE ...
  • 第一个假如有一个数据表sm_movies里面有重复的电影想去掉重复的并且保存一个movies_id值最大的那一个DELETEFROM`sm_movies`WHEREmovies_idNOTIN(SELECT*FROM(SELECTMAX(movies_id)asmovies_idFROM`sm_movies`...
  • oracle某个字段有重复数据,如何删除多余数据只保留1条 Oracle 删除重复数据只留一条 查询及删除重复记录SQL语句 1、查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断 select * from 表 where ...
  • 查询及删除重复记录SQL语句1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) ...
  • select * into tmp_lzw1 From tb_sc_user_cz200905 a where exists (select * From dhhm b where b.col001=a.servnumber)select servnumber,max(chgamt) as chamt,recdate into tmp_lzw2 from tmp_lzw1 where chgamt...
  • sql语句查询重复的数据

    千次阅读 2021-05-19 09:50:15
    查找所有重复标题的记录: ...查找重复记录 1。查找全部重复记录 Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1) 2。过滤重复记录(只显示一条) Select * Fr
  • mysql批量删除sql语句

    千次阅读 2021-02-02 09:55:42
    30种mysql优化sql语句查询的方法_计算机软件及应用_IT/计算机_专业资...简要介绍基础语句: 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份 sql server --...
  • 本文章总结了同时删除多个数据表与同时删除多个数据表的关系数据的方法,有需要的朋友可参考一下。批量删除多表删除所有pre_前缀的表代码如下复制代码SELECT CONCAT( 'drop table ',table_name,'; ') FROM ...
  • 查询重复数据代码如下select * from tableA where catentry_id in (select distinct catentry_id from tableA group by catentry_id,descitem_id having count(*)>1)@--2.建立临时表,将所有重复数据存入该表代码...
  • 在数据开发时我们常常会需要把数据库中重复的记录查出来或直接删除数据库中重复记录,下面我来给大家总结一些方法,有需要的朋友可参考。常用的语句1、查找表中多余的重复记录重复记录是根据单个字段(mail_id)来...
  • 根据单个字段(title)查找表中多余的重复记录 select * from article where title in (select title from article group by title having count(title) > 1) 根据多个字段查找表中多余的重复记录 select *...
  • SQL语句添加删除修改字段

    千次阅读 2021-02-02 06:00:42
    SQL语句添加删除修改字段1.增加字段alter table docdsp add dspcode char(200)2.删除字段ALTER TABLE table_NAME DROP COLUMN column_NAME3.修改字段类型ALTER TABLE table_name ALTER COLUMN column_name new_...
  • 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢 1、查找表中多余的重复记录重复记录是根据单个字段(code)来判断 select * from MLTY_MDM_PRODUCT where code in (select code from MLTY_MDM...
  • SQL语句添加删除修改字段

    千次阅读 2021-01-19 04:48:45
    SQL语句添加删除修改字段1.增加字段alter table docdsp add dspcodechar(200)2.删除字段ALTER TABLE table_NAME DROP COLUMNcolumn_NAME3.修改字段类型ALTER TABLE table_name ALTER COLUMNcolumn_name new_data_...
  • sql server ql语句删除外键和删除alter table tablename add constraint ordersrelationshipforeign key mployeeidreferences employees (employeeid)on delete cascadeon update cascadealter...
  • 1.如何用SQL语句把一个表里面某字段内有相同的记录查询出来现在有一个表 有三列 分别是tel,name,addr我现在要查询name一样的具体T-SQL语句要怎么写 求助就是name这个列有很多记录在数据库里面 这些人有很多哈 就是...
  • 展开全部查询可32313133353236313431303231363533e58685e5aeb931333337613931用group by语句删除则用delete语句。1、创建测试表,插入测试数据:createtabletest(idint,namevarchar2(20));insertintotestvalues(1,...
  • select max([id]) from YourTable group by (name + value)) 方法2 delete a from 表 a left join( select (id) from 表 group by name,value )b on a.id=b.id where b.id is null 查询及删除重复记录SQL语句 查询...
  • 引用:https://blog.csdn.net/dsiori/article/details/52806709正好想写一条删除重复语句并保留一条数据的SQL,网上查了...如下:表字段和数据:SQL语句:DELETEFROM`user`WHEREidNOTIN(SELECT*FROM(SELECTidFROM`us...
  • 当测试数据库中,有重复数据会导致selectOne报错时,可以使用这句sql,只保留id最小的一条数据,删除其余的重复数据。 二、场景实例 例如有下方这样的数据: id | name | openid 1 | 张三 | 2_0_10001234 2 ...
  • SQL语句,删除重复项只保留一条 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除重复的呢?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 168,175
精华内容 67,270
关键字:

sql语句删除重复记录