解决:ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list等问题

发布于:2023-09-14 ⋅ 阅读:(108) ⋅ 点赞:(0)

问题描述:

不同数据库中报错信息略有差别;

MySQL中报错:

Expression #1 of ORDER BY clause is not in SELECT list, references column 'public.student.id' which is not in SELECT list; this is incompatible with DISTINCT

PostgreSQL报错:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

错误原因:

当使用distinct函数时排序字段没有出现在select后面;

举例:

表数据:

错误示例:

//可以看到排序字段是id,但是id没有出现在select distinct后面,所以会报错
select distinct name,age,class from student where name = '张三' order by id;

正确示例:

将排序字段id也添加到select distinct后面输出就好了

select distinct id, name,age,class from student where name = '张三' order by id;

示例结果: