DISTINCT 与ORDER BY执行顺序
大家都知道DISTINCT用于去重,但是需要注意的是去重时是自带排序的,而ORDER BY也是用于排序,所以当两者一起使用不当会存在冲突报错,比如PostgreSQL数据库执行如下语句
select distinct emp_id
from userInfo
order by emp_name
SQL 错误 [42P10]: ERROR:
for SELECT DISTINCT, ORDER BY expressions
must appear in select list
Position: 60
这个报错是指在使用SELECTDISTINCT的同时,你的ORDER BY子句中使用的某些表达式并没有出现在SELECT列表中。
要理解报错原因就需要了解两者的执行顺序了。
执行顺序优先级是SELECT DISTINCT关键字先执行,ORDER BY后执行
所以由于执行顺序的问题会要求ORDER BY中的字段一定要出现在DISTINCT列表中,不然ORDER BY排序时就找不到这个排序字段,肯定就会报错了
因此调整把排序字段添加到SELECT列就可以解决该问题,示例如下:
select distinct emp_name,emp_id
from userInfo
order by emp_name
进阶问题:
是不是把列加到SELECT列中就一定不会报错了呢,这里还有一个坑,就是当ORDER BY排序字段涉及到排序规则时
比如我建了一个userInfo表,默认排序规则是default
当我ORDER BY排序时需要指定另一个排序规则时,如果按照如上写法就会报错
select distinct
emp_id,
emp_name
from userInfo
order by emp_name collate "zh_CN.utf8"
SQL 错误 [42P10]: ERROR:
for SELECT DISTINCT, ORDER BY expressions
must appear in select list
Position: 78
此时我们发现报的错跟上面一样,这个大家是不是就奇怪了,明明已经在SELECT列中了,为啥还会报错,但把DISTINCT去掉后,即如下脚本则不会报错,能够正常查询
select emp_id,emp_name
from userInfo
order by emp_name collate "zh_CN.utf8"
这个原因是因为没有DISTINCT关键字的时候,ORDER BY关键字先执行,SELECT 关键字后执行
而上面报错的原因还是跟执行顺序有关,因为SELECT DISTINCT先执行,ORDER BY字段指定字符集后会创建一个新字段,该字段不在SELECT列表中,所以还是报同样的错。
解决办法如下:
select
distinct
emp_id,
emp_name,
emp_name collate "zh_CN.utf8" as emp_name_utf8
from
userInfo
order by
emp_name_utf8
直接在SELECT列中对排序字段改变字符集,并取别名,ORDER BY直接对该别名字段进行排序即可。