oracle部门平均工资视图,在Oracle中创建一个显示每个部门平均工资的视图？(Creating a view in Oracle that will show the average salar...

在Oracle中创建一个显示每个部门平均工资的视图？(Creating a view in Oracle that will show the average salary of each department?)

我想在Oracle中查看将显示每个部门的平均薪水。 我使用的表是Employee，它有empno ， salary和deptname字段。

我试着这样做：

create view v_employee

as select avg(salary),deptname as average_salary from employee

order by name;

这给了我错误：

不是单一的群体功能

所以我试着把它们分组

create view v_employee

as select avg(salary),deptname as average_salary from employee

group by salary

order by name

这给了我错误：

不是GROUP BY表达式

我该怎么做才能看到这个观点？ 谢谢

I wish to make a view in Oracle that will show the average salary in each department. The table that I am using is Employee which has empno, salary and deptname fields.

I tried to make it by doing this:

create view v_employee

as select avg(salary),deptname as average_salary from employee

order by name;

which gave me the error:

not a single group function

so then I tried to group them by doing

create view v_employee

as select avg(salary),deptname as average_salary from employee

group by salary

order by name

which gave me the error:

not a GROUP BY expression

What can I do to make this view? Thanks

原文：https://stackoverflow.com/questions/47022920

更新时间：2020-02-17 12:02

最满意答案

select avg(salary),deptname as average_salary

您已请求2列，第一列没有标签，并且您已将重新标记的deptname设置为“average_salary”。 所以你可能真的想要：

select deptname, avg(salary) as average_salary

为了产生平均值，必须进行一些算术运算，其中考虑了每个部门的所有工资，即我们将数据“分组”为每个部门的一行。

select deptname, avg(salary) as average_salary from employee

group by deptname

对于select子句中的每一列，我们没有使用像AVG()这样的聚合函数，我们必须在group by子句中使用相同的列。 如果不这样做，您将收到有关“不是按表达式分组”的错误消息。

所有我们可以添加创建视图代码：

create view v_employee as

select deptname, avg(salary) as average_salary from employee

group by deptname

但是，您不应该在视图中下order by因为这可能非常低效。 当您使用视图是后续查询时，那么包括例如

select *

from v_employee

where deptname like 's%'

order by average_salary DESC

在该示例中，视图内的任何嵌入顺序都将被丢弃，因为我们现在想要的是最高的平均值。 另外，因为我们使用了where子句，所以我们只需要订购几行，而不是所有行。

select avg(salary),deptname as average_salary

You have requested 2 columns, for the first column there is no label, and you have relabeled deptname to "average_salary". So you probably really wanted:

select deptname, avg(salary) as average_salary

To produce an average there has to be some arithmetic performed which takes into account all the salaries across each department, i.e. we "group" the data into one row for each department.

select deptname, avg(salary) as average_salary from employee

group by deptname

For every column in the select clause, where we have not used an aggregation function like AVG(), we must used that same column in the group by clause. If you don't you will get error messages about "not a group by expression".

Ater all that we can add the create view code:

create view v_employee as

select deptname, avg(salary) as average_salary from employee

group by deptname

BUT, you should never place an order by into a view as this is potentially very inefficient. When you use the view is a subsequent query, THEN include an order by e.g.

select *

from v_employee

where deptname like 's%'

order by average_salary DESC

In that example any embedded order inside the view would simply be discarded because what we want now is the highest average first. Also, because we have used a where clause, we only need to order a few rows, not all of them.

2017-10-31

相关问答

您可以使用： var massagedEmployees = employees.GroupBy(e => e.Department)

.Select(g => new { Department = g.Key, Avg = g.Average(e => e.Salary) } );

You can use: var massagedEmployees = employees.GroupBy(e => e.Department)

...

样本数据 我添加了几行具有更宽树结构的行。 DECLARE @Employee TABLE

(

Id INT NOT NULL ,

Name VARCHAR(200) NOT NULL ,

Department_Id INT NOT NULL ,

PRIMARY KEY ( Id )

);

DECLARE @Department TABLE

(

Id INT NOT NULL ,

DepartmentName VARCHAR(200) NOT NULL ,

Parent_Id INT ,

PR

...

退还一名每个部门薪水最高的员工。 使用DISTINCT ON可以实现更简单，更快速的查询，满足您的所有需求： SELECT DISTINCT ON (d.id)

d.id AS department_id, d.name AS department

,e.id AS employee_id, e.name AS employee, e.salary

FROM departments d

LEFT JOIN employees e ON e.department_i

...

我们可以在没有子查询的情况下完成吗？ 不是我能想到的。 如果条件>=那么以下就行了 SELECT TOP 1 WITH TIES *

FROM employee

ORDER BY CASE

WHEN employee_salary >= AVG(employee_salary)

OVER (

PARTITION BY

...

尝试这个 SELECT Dept_no,

if(Dept_no='C1',salary,0) as Dept_noC1,

if(Dept_no='C2',salary,0) as Dept_noC2,

if(Dept_no='C3',salary,0) as Dept_noC3

From Emp

Group by Dept_no;

小提琴演示 输出： +---------+--------+--------+--------+

| Dept_no | D

...

select avg(salary),deptname as average_salary

您已请求2列，第一列没有标签，并且您已将重新标记的deptname设置为“average_salary”。 所以你可能真的想要： select deptname, avg(salary) as average_salary

为了产生平均值，必须进行一些算术运算，其中考虑了每个部门的所有工资，即我们将数据“分组”为每个部门的一行。 select deptname, avg(salary) as avera

...

然后，您需要按部门分组并计算平均工资，按其排序。 如果Department.DEPTNM不是唯一的，我们按DEPTID分组： SELECT Employee.EMPDEPTID, AVG(Employee.SALARY) as AVERAGE_SALARY

FROM Employee

GROUP BY Department.DEPTID

ORDER BY AVG(Employee.SALARY)

如果您需要获得部门名称，那么您可以在以下情况后加入Department ： SELECT * FR

...

只需向WHERE子句添加第二个条件： select first_name, last_name, salary

from staff

where salary > (select avg(salary) from staff) and

salary < (select min(salary) from staff where role = 'supervisor')

我正在解释你想要添加一个条件，即薪水低于任何主管的逻辑。 如果只有一个主管，则上述查询仍应正确。 Just add a

...

select departments.department_name

, avg(employees.salary) as avgsalary

from departments inner join employees

on departments.department_id = employees.department_id

group by departments.department_name

having avg(employees.salary) > 5000 and

...