分组功能和分组依据

debugcn 发表于 Dev

布赛德

我有以下查询：

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;

返回的结果不是所需的顺序。但是，当尝试使用别名“平均工资”或1时，查询将按预期和期望的方式工作。

提到的查询结果：

使用别名或数字时的结果：

为什么？

康纳·麦克唐纳

是的，多数民众赞成在一个错误。我们在寻找机会来消除分组成本，在这种情况下，某些事情将变得不对劲。您可以解决这个问题

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.

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。