mysql根据多个字段去重并保留最小id的记录

最新推荐文章于 2024-03-27 11:00:13 发布

doubleclicksixsixsix

最新推荐文章于 2024-03-27 11:00:13 发布

阅读量1.6k

点赞数

本文链接：https://blog.csdn.net/qq_36530221/article/details/104753964

版权

mysql:

update table set deleted = 1 where (column1,column2,column3) 

in 

(select t1.column1,t1.column2,t1.column3 from (select column1,column2,column3 from table group by column1,column2,column3 having count(*) > 1) t1) 

and id not in

(select t2.minid from (select min(id) minid from table group by column1,column2,column3 having count(*) > 1) t2)

扩展： java8 list根据多属性去重，时间类型可以直接转换：

List<User> collectList = list.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() ->
        new TreeSet<>(Comparator.comparing(u -> u.getName() + ";" + u.getAge() + ";" + DateFormatUtils.format(u.getCreateTime(), "yyyy-MM-dd")))), ArrayList::new));