mysql 过滤多列重复的值（保留其中一条），对单列或者多列重复的值去重

建立测试数据表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `deviceId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (10, 'A', '123456', 'A111');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (20, 'B', '1234562', 'A222');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (30, 'C', '123456', 'A333');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (40, 'D', '1234563', 'A444');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (50, 'E', '1234561', 'A111');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (60, 'C', '1234561', 'A333');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (70, 'C', '1234564', 'A333');
INSERT INTO `test`.`test` (`id`, `account`, `password`, `deviceId`) VALUES (80, 'D', '1234565', 'A444');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

根据单列去重

-- 找到哪些是重复的行
-- 赛选需要留下的行
-- 从重复的行去除留下的行，得到需要排除的行
SELECT
	* 
FROM
	test 
WHERE
	id NOT IN (
	SELECT
		id 
	FROM
		test 
	WHERE
		account IN ( SELECT account FROM test GROUP BY account HAVING count( account ) > 1 ) 
		AND id NOT IN ( SELECT max( id ) FROM test GROUP BY account HAVING count( account ) > 1 ) 
	)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

根据多列去重

-- group by 之后使用的聚合函数都是统计组内的信息，
-- only_full_groupby模式要明确分组之后select的值应该取哪一个，如果取的字段无法适用于任何聚合函数的话，可以用any_value 但是any_value到底是取得哪一个呢，常用的场景是先排序再分组取第一个
-- 需要子查询或者join，根据查询的重复字段去原始记录表匹配
SELECT
	* 
FROM
	test 
WHERE
	id NOT IN (
	SELECT
		id 
	FROM
		test 
	join  ( SELECT account,deviceId FROM test GROUP BY account, deviceId HAVING count( account ) > 1 and count(deviceId) > 1 ) as ref 
	on ref.account = test.account and ref.deviceId = test.deviceId
		AND id NOT IN ( SELECT min(id) FROM test GROUP BY account, deviceId HAVING count( account ) > 1 and count(deviceId) > 1 ) 
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在查找所有的重复列的时候，可以用子查询去匹配也可以用join。看哪个用的熟练了。

简介版

SELECT
	* 
FROM
	test 
WHERE
	id  IN (SELECT min(id) AS id
      FROM test
      GROUP BY account, deviceId)
1
2
3
4
5
6
7
8

参考

https://blog.csdn.net/qq_35387940/article/details/108074927