具有group by的order by group函数

sbdsn5lh 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(279)

我有以下疑问：

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 salary”或1时，查询的效果与预期的一样。
上述查询的结果：

使用别名或号码时的结果：

为什么？

sql group-by oracle sql-order-by

来源：https://stackoverflow.com/questions/62599620/order-by-group-function-with-group-by

1条答案

按热度按时间

yk9xbfzb1#

是的，那是个虫子。我们寻找机会，以消除成本的小组，在这种情况下，是出了问题。您可以将此作为解决方法进行提示

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.

赞(0）回复(0）举报 2021-07-24

我来回答

具有group by的order by group函数

1条答案

相关问题

热门标签

最新问答