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');

根据单列去重

-- 找到哪些是重复的行
-- 赛选需要留下的行
-- 从重复的行去除留下的行，得到需要排除的行
SELECT* 
FROMtest 
WHEREid NOT IN (SELECTid FROMtest WHEREaccount 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 ) )

根据多列去重

-- group by 之后使用的聚合函数都是统计组内的信息，
-- only_full_groupby模式要明确分组之后select的值应该取哪一个，如果取的字段无法适用于任何聚合函数的话，可以用any_value 但是any_value到底是取得哪一个呢，常用的场景是先排序再分组取第一个
-- 需要子查询或者join，根据查询的重复字段去原始记录表匹配
SELECT* 
FROMtest 
WHEREid NOT IN (SELECTid FROMtest 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.deviceIdAND id NOT IN ( SELECT min(id) FROM test GROUP BY account, deviceId HAVING count( account ) > 1 and count(deviceId) > 1 ) )

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

简介版

SELECT* 
FROMtest 
WHEREid  IN (SELECT min(id) AS idFROM testGROUP BY account, deviceId)

参考

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