order by group函数与group by

Question

我有以下疑问：

select round(avg(employees.salary)) as "Average salary",
count(1) as "Number of employees",
employees.department_id as "Department ID",
departments.department_name as "Department Name"
from employees, departments
where employees.department_id = departments.department_id
group by employees.department_id, departments.department_name
order by round(avg(employees.salary)) desc;

返回的结果不是期望的顺序。但是，当尝试使用别名"Average desired“或1时，查询就会按照预期和期望工作。

上述查询的结果：

​

​

当我使用别名或数字时，结果是：

​

​

为什么？

Answer 1

是啊，那是个bug。我们寻找机会消除group by的成本，在这种情况下，有些地方出了问题。您可以将此作为一种解决方法进行提示

SQL> select round(avg(employees.salary)) as "Average salary"
  2  from hr.employees, hr.departments
  3  where employees.department_id = departments.department_id
  4  group by employees.department_id, departments.department_name
  5  order by round(avg(employees.salary)) desc;

Average salary
--------------
          8956
          3476
         19333
          8601
          5760
          4150
         10154
          9500
         10000
          6500
          4400

11 rows selected.

SQL> select /*+ opt_param('_optimizer_aggr_groupby_elim', 'false')*/ round(avg(employees.salary)) as "Average salary"
  2  from hr.employees, hr.departments
  3  where employees.department_id = departments.department_id
  4  group by employees.department_id, departments.department_name
  5  order by round(avg(employees.salary)) desc;

Average salary
--------------
         19333
         10154
         10000
          9500
          8956
          8601
          6500
          5760
          4400
          4150
          3476

11 rows selected.