MySQL查询、删除重复记录

2018/11/04 23:37
阅读数 128

表结构,数据如下:

CREATE TABLE `redis_num_mem` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `cluster_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Redis集群id',
  `type` varchar(10) NOT NULL DEFAULT '' COMMENT 'key类型',
  `number` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'key的个数',
  `memory` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'key的存内,单位bytes',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '记录生成时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8 COMMENT='redis类型表'


MariaDB [test]> select * from redis_num_mem;
+-----+------------+--------+--------+--------+---------------------+
| id  | cluster_id | type   | number | memory | create_time         |
+-----+------------+--------+--------+--------+---------------------+
|   1 |          1 | hash   |  47079 |    100 | 2018-10-22 23:48:12 |
|   2 |          1 | set    |  20608 |    100 | 2018-10-22 23:48:12 |
|   3 |          1 | list   |   3938 |    100 | 2018-10-22 23:48:12 |
|   4 |          1 | string |  17662 |    100 | 2018-10-22 23:48:12 |
|   5 |          1 | hash   |  46808 |    100 | 2018-10-22 23:48:12 |
|   6 |          1 | set    |  20854 |    100 | 2018-10-22 23:48:12 |
|   7 |          1 | string |  17710 |    100 | 2018-10-22 23:48:12 |
|   8 |          1 | list   |   3917 |    100 | 2018-10-22 23:48:12 |
|   9 |          1 | list   |   3854 |    100 | 2018-10-22 23:48:12 |
|  10 |          1 | string |  17651 |    100 | 2018-10-22 23:48:12 |
|  11 |          1 | hash   |  46960 |    100 | 2018-10-22 23:48:12 |
|  12 |          1 | set    |  20654 |    100 | 2018-10-22 23:48:12 |
|  14 |          2 |        |      0 |      0 | 2018-10-23 10:34:54 |
|  15 |          2 |        |      0 |      0 | 2018-10-23 10:35:42 |
|  16 |          0 |        |      0 |      0 | 2018-10-23 10:50:39 |
|  20 |          0 |        |      0 |      0 | 2018-10-23 11:05:52 |
| 100 |          0 |        |      0 |      0 | 2018-10-23 11:05:38 |
| 101 |          0 |        |      0 |      0 | 2018-10-23 11:06:09 |
+-----+------------+--------+--------+--------+---------------------+
18 rows in set (0.00 sec)

查找所有重复类型的记录:

SELECT * FROM redis_num_mem a WHERE ((SELECT COUNT(*) FROM redis_num_mem WHERE type = a.type) > 1) ORDER BY type DESC;

一、查找重复记录

1.1  查找全部重复记录

Select * From redis_num_mem Where type In (Select type From redis_num_mem Group By type Having Count(*)>1) ;

1.2  过滤重复记录(只显示一条)

select * From redis_num_mem where id in (Select Max(id) From  redis_num_mem Group By type);

注:此处显示ID最大一条记录

二、删除重复记录

2.1  删除全部重复记录(慎用)

delete from redis_num_mem where type in (select type from (select type from redis_num_mem group by type having count(*)>1)  as t1);

2.2  保留一条(这个应该是大多数人所需要的 ^_^)

delete from redis_num_mem where id not in (select * from (select max(id) from redis_num_mem group by type) as t1);

三、补充

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)

 

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)

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部