mysql报错ORDER BY clause is not in SELECT list, references column ‘‘which is not in SELECT list解决方案

小半 • 2023年2月17日下午5:46 • 技术随笔 • 阅读 319

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{

	//LinkedHashMap有序去重
	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;
	}
	
	//ConcurrentHashMap无序去重
	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);