精华内容
下载资源
问答
  • 创建表 CREATE TABLE `user` ( `name` varchar(10) DEFAULT NULL, `age` int(3) DEFAULT NULL, ...删除重复数据 DELETE FROM `user` WHERE (`name`, `age`, `sex`) IN ( SELECT a.`name`, a.`age`,

    创建表

    CREATE TABLE `user` (
      `name` varchar(10) DEFAULT NULL,
      `age` int(3) DEFAULT NULL,
      `sex` char(1) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    删除重复数据

    DELETE
    FROM
    	`user`
    WHERE
    	(`name`, `age`, `sex`) IN (
    		SELECT
    			a.`name`,
    			a.`age`,
    			a.`sex`
    		FROM
    			(
    				SELECT
    					`name`,
    					`age`,
    					`sex`
    				FROM
    					`user`
    				GROUP BY
    					`name`,
    					`age`,
    					`sex`
    				HAVING
    					count(1) > 1
    			) a
    	)
    LIMIT 1;
    
    展开全文
  • 有这样一张表,表数据及结果如下:school_idschool_nametotal_studenttest_takers1239Abraham Lincoln High School55501240Abraham Lincoln High School70351241Acalanes High School120891242Academy Of The ...

    有这样一张表,表数据及结果如下:

    school_id

    school_name

    total_student

    test_takers

    1239

    Abraham Lincoln High School

    55

    50

    1240

    Abraham Lincoln High School

    70

    35

    1241

    Acalanes High School

    120

    89

    1242

    Academy Of The Canyons

    30

    30

    1243

    Agoura High School

    89

    40

    1244

    Agoura High School

    100

    50

    我们可以看出,school_name的字段值有重复数据(Abraham Lincoln High School 和Agoura High School分别出现两次),那么如何删除这两条数据,从而只让这两个数值出现一次呢? 具体实现方法如下:

    1、删除重复记录,保存Id最小的一条

    delete FROM `test` WHERE `school_name` in (SELECT `school_name`

    FROM `test`

    GROUP BY `school_name`

    HAVING COUNT( * ) >1) and school_id not in (select min(school_id) from test group by school_id having count(* )>1)

    先使用GROUP BY having语法查询出重复的数据,然后删除重复数据并保留school_id最小的一条.

    2、删除重复记录,保存Id最大的一条

    delete FROM `test` WHERE `school_name` in (SELECT `school_name`

    FROM `test`

    GROUP BY `school_name`

    HAVING COUNT( * ) >1) and school_id not in (select max(school_id) from test group by school_id having count(* )>1)

    原理和上面一样。

    以上就是mysql查找删除重复数据并只保留一条实例详解,希望能帮助到大家,谢谢大家对本站的支持!

    展开全文
  • mysql表中,某一字段的值有很多重复数据,需要将这些重复的数据记录删除,只保留其中的一条数据即可,如何使用SQL语句来实现呢,本文章向大家介绍mysql如何查找删除重复数据,需要的朋友可以参考一下。 有这样一张...

    mysql表中,某一字段的值有很多重复数据,需要将这些重复的数据记录删除,只保留其中的一条数据即可,如何使用SQL语句来实现呢,本文章向大家介绍mysql如何查找删除重复数据,需要的朋友可以参考一下。

    有这样一张表,表数据及结果如下:

    school_idschool_nametotal_studenttest_takers
    1239Abraham Lincoln High School5550
    1240Abraham Lincoln High School7035
    1241Acalanes High School12089
    1242Academy Of The Canyons3030
    1243Agoura High School8940
    1244Agoura High School10050

    我们可以看出,school_name的字段值有重复数据(Abraham Lincoln High School 和Agoura High School分别出现两次),那么如何删除这两条数据,从而只让这两个数值出现一次呢? 具体实现方法如下:

    1、删除重复记录,保存Id最小的一条

    delete FROM `test` WHERE `school_name` in (SELECT `school_name`
    FROM  `test` 
    GROUP BY  `school_name` 
    HAVING COUNT( * ) >1) and school_id not in (select min(school_id) from test group by school_id having count(* )>1)

    先使用GROUP BY having语法查询出重复的数据,然后删除重复数据并保留school_id最小的一条.

     

    2、删除重复记录,保存Id最大的一条

    delete FROM `test` WHERE `school_name` in (SELECT `school_name`
    FROM  `test` 
    GROUP BY  `school_name` 
    HAVING COUNT( * ) >1) and school_id not in (select max(school_id) from test group by school_id having count(* )>1)

    原理和上面一样。 

    展开全文
  • mysql 删除重复数据只保留一条 多字段写法(单字段去掉其中一个即可,删掉Id最小的那一条) delete FROM monitor_city_day_average_release WHERE (time, code) IN ( SELECT t.time, t.code FROM ( SELECT time, ...

    mysql 删除重复数据只保留一条

    多字段写法(单字段去掉其中一个即可,删掉Id最小的那一条)

    delete
    FROM
    monitor_city_day_average_release
    WHERE
    (time, code) IN (
    SELECT
    t.time,
    t.code
    FROM
    (
    SELECT
    time, code
    FROM
    monitor_city_day_average_release
    GROUP BY
    time, code
    HAVING
    count(1) > 1
    ) t
    )

    AND id NOT IN (
    SELECT
    dt.id
    FROM
    (
    SELECT
    min(id) AS id
    FROM
    monitor_city_day_average_release
    GROUP BY
    time, code
    HAVING
    count(1) > 1
    ) dt
    );
    commit;

    展开全文
  • SELECT * FROM (SELECT addTime FROM motorcade.car_msg_info GROUP BY addTime HAVING COUNT(addTime) > 1) AS b) AND ID NOT IN ...MIN(ID)表示保留ID最小的一条 换成MAX可以保留最新的一条 addTime代表重复数据
  • -- mysql删除重复数据只保留一条 DELETE FROM general_dd_dept_teacher WHERE id NOT IN ( select a.id from (SELECT min( id ) AS id FROM general_dd_dept_teacher GROUP BY dd_dept_id, dd_teacher_id,sys_...
  • 清洗数据的时候,发现有重复数据。之前多用EXCEL处理数据,一键就可以delete了。这次因为数据量相对较大换了mysql。按照百度搜到的个高票代码,运行了半天都不对。一直报错:即按照mysql语言逻辑不能先select出...
  • 自己建个如下表直接上sql 亲自测试 真实有效----针对单个字段重复数据delete FROM test a WHERE a.address in ( select s.address from ( SELECT t.addressFROM test tGROUP BY t.addressHAVING COUNT( * ) >...
  • 在几千记录里,存在着些相同的记录,如何能用SQL语句,删除重复的呢1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select * from peoplewhere peopleId in (select peopleId from people ...
  • 用SQL语句,删除重复只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除重复的呢1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断SELECT*FROMpeopleWHEREpeopleId IN ...
  • 目的: 一张表,表名credit_user相同的user_code有多条重复数据,现在只保留一条数据一:首先是这么想的DELETEFROMcredit_userWHEREuser_code IN (SELECTuser_codeFROMcredit_userGROUP BYuser_codeHAVINGcount(user...
  • id, community_code HAVING COUNT(*) = 1) nd ) 中间的临时表不能省,mysql会报错:You can't specify target table 'pay_vip_point' for update in FROM clause 下面是错误示例: DELETE FROM pay_vip_point WHERE ...
  • 准备数据
  • 这两天做了个调用第三方接口的小程序,因为是实时更新数据,所以请求接口的频率就很高,这样有时会出现往数据库插入重复数据,对数据库造成压力也不方便管理,因为要通过原生sql语句,解决数据库的去重问题....
  • 测试1,删除重复数据保留一条,有效。但适合有两条重复的数据,因为group时只能查到一个ID,所以每次只能删除一条。DELETE FROM table_a WHERE record_id IN (SELECT a.id FROM (SELECT id FROM table_a GROUP ...
  • 有这样一张表,表数据及结果如下:可以看出,school_name的字段值有重复数据(Abraham Lincoln High School 和Agoura...//删除重复记录,保存Id最小的一条delete FROM `test` WHERE `school_name`in(SELECT `school_na...
  • 伙伴们经常线上会有一些脏数据我们要进行去重处理,例如我们有一张user表里面有三条数据,现需求按照age进行删除重复数据,每个年龄只能保留一条并且是最新的一条 1.我们先来验证一下重复数据 -- 验证sql ...
  • MySql删除重复数据 1. 问题引入 前一段遇到MySql数据重复的问题。由于重复向同一张表导入同一批数据,导致前台展示的数据重复,唯一方便快捷的方法莫过于利用delete操作删除重复数据(已经封板发布),只保留其中一条...
  • 删除表中重复记录,只保留一条:delete from 表名 where 字段ID in (select * from (select max(字段ID) from 表名 group by 重复的字段 having count(重复的字段) > 1) as b);实例: 2.当想要为某一个表建立一个...
  • 表结构如下:mysql> desc test1;+--------------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-------...
  • MySQL删除重复数据保留最新的一条,网上有些博客写的有问题,因为他们考虑到或者说他们的重复数据最多只有两条,当重复数据大于2时,就会出现问题。 案例描述: 按天删除表中重复数据只保留最新的一条(这里...
  • mysql 删除重复数据保留一条数据,没有主键(即没有rowid)现有表信息数据通过分组查询出重复数据为通过limit数据删除重复数据保留一条 现有表信息数据 通过分组查询出重复数据为 select id, COUNT(id) as ...
  • DELETE FROM student WHERE id NOT IN ( SELECT t.id FROM ( SELECT MIN( id ) AS id FROM student GROUP BY name ) t )
  • 1、查找重复数据:SELECT*FROM`table_a`asaWHERENOTEXISTS(SELECT*FROMtable_basbWHEREb.a_id=a.id))ascGROUPBYc.titleHAVINGcount(1)>...2、查找重复数据中需要保留的数据:SELECTMIN(c.id)asidFROM(SELECT*...
  • 在几千记录里,存在着些相同的记录,如何能用SQL语句,删除重复的呢1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断SELECT*FROMpeopleWHEREpeopleIdIN...1)2、删除表中多余的重复记录,重复记录...
  • 删除表中重复记录,只保留一条:delete from 表名 where 字段ID in (select * from (select max(字段ID) from 表名 group by 重复的字段 having count(重复的字段) > 1) as b);实例:2.当想要为某一个表建立一个...
  • #删除重复数据只保留一条 DELETE FROM internal_book_car WHERE id NOT IN ( SELECT dt.id FROM ( SELECT MIN( id ) id, passId FROM internal_book_car GROUP BY passId ) dt ) 此条语句再执行大数据量表...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 36,458
精华内容 14,583
关键字:

mysql删除重复数据只保留一条

mysql 订阅