mysql报错Expression #1 of ORDER BY clause is not in SELECT list, references column 'fusion.m.create_time' which is not in SELECT list; this is incompatible with DISTINCT解决方案
- 问题背景
- 解决方案
- 总结
- Lyric: 谁说拍中国风 一定要配灯笼
问题背景
在进行多表关联出现报错:Expression #1 of ORDER BY clause is not in SELECT list, references column ‘fusion.m.create_time’ which is not in SELECT list; this is incompatible with DISTINCT,原因是有个字段的order by排序和distinct不兼容
解决方案
1 首先在xml的distinct去重去除
2 把所有查出来的数据在程序里面进行单独去重,一般查出来的都是list<对象>类型,根据对象里面的某一个字段进行去重,可以使用流来进行操作
public class StreamUtils{
private static <T> Predicate<T> distinctByKey(Function<? super T, Object> keyExtractor) {
LinkedHashMap<Object, Boolean> map = new LinkedHashMap<>();
return t -> map.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
}
private static <T> Predicate<T> distinctByKeyMap(Function<? super T, Object> keyExtractor) {
ConcurrentHashMap<Object, Boolean> map = new ConcurrentHashMap<>();
return t -> map.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
}
}
List<User> list = new ArrayList<User>();
list.add(new User("小南", 23, "18335888888"));
list.add(new User("小南", 22, "18335888888"));
list.add(new User("小南", 21, "18335888888"));
list.add(new User("小南", 20, "18335888888"));
list = list.stream().filter(StreamUtils.distinctByKey(User :: getName)).collect(Collectors.toList());
System.out.println(list.toString());
List<User> userList = new ArrayList();
userList.add(new User("yuange", 12));
userList.add(new User("yuange", 12));
userList.add(new User("yuange1", 14));
userList.add(new User("yuange1", 15));
userList.add(new User("pangpang", 12));
userList.add(new User("pangpang", 12));
List<User> result = userList.stream()
.collect(collectingAndThen(toCollection(() ->
new TreeSet<>(comparing(User::getName))), ArrayList::new));
result.forEach(System.out::println);
总结
看过别人的一些解决方案,一般是更改数据库配置,或者使用聚合函数,但我都无效,所以使用自己的方式
作为程序员第 195 篇文章,每次写一句歌词记录一下,看看人生有几首歌的时间,wahahaha …
Lyric: 谁说拍中国风 一定要配灯笼
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)