表结构，数据如下：

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)

IT--小哥