我看到了很多关于如何递归地查询自引用表的问题/答案,但是我很难将我找到的答案应用到每个父级、祖父级等等,而不管该项在层次结构中的位置。
需要获取每个部门的平均薪资,包括层次结构。 这意味着部门应该包括每个子部门等的平均工资。
我有一个nex数据库模式:
CREATE TABLE Employee
(
Id INT NOT NULL ,
Name VARCHAR(200) NOT NULL ,
Department_Id INT NOT NULL ,
PRIMARY KEY ( Id )
);
CREATE TABLE Department
(
Id INT NOT NULL ,
DepartmentName VARCHAR(200) NOT NULL ,
Parent_Id INT ,
PRIMARY KEY ( Id )
);
CREATE TABLE Salary
(
Id INT NOT NULL ,
Date DATETIME NOT NULL ,
Amount INT NOT NULL ,
Employee_Id INT NOT NULL ,
PRIMARY KEY ( Id )
);
我尝试了类似的方法,但它只包括层级结构的第一层。
SELECT d.Id ,
d.DepartmentName ,
( SELECT AVG(s.Amount)
FROM dbo.Department dd
LEFT JOIN dbo.Department sdd ON dd.Id = sdd.Parent_Id
JOIN dbo.Employee e ON e.Department_Id = sdd.Id
OR e.Department_Id = dd.Id
JOIN dbo.Salary s ON s.Employee_Id = e.Id
WHERE dd.Id = d.Id
) AS avg_dep_salary
FROM dbo.Department d
WHERE d.Parent_Id IS NULL;
如何获得所有级别的平均工资?
编辑:添加了一些插入内容。
INSERT INTO Employee
( Id, Name, Department_Id )
VALUES ( 1, 'Peter', 1 ),
( 2, 'Alex', 1 ),
( 3, 'Sam', 2 ),
( 4, 'James', 2 ),
( 5, 'Anna', 3 ),
( 6, 'Susan', 3 ),
( 7, 'Abby', 4 ),
( 8, 'Endy', 4 );
INSERT INTO Department
( Id, DepartmentName, Parent_Id )
VALUES ( 1, 'IT', NULL ),
( 2, 'HR', NULL),
( 3, 'SubIT', 1 ),
( 4, 'SubSubIT', 3 );
INSERT INTO Salary
( Id, Date, Amount, Employee_Id )
VALUES ( 1, '2013-01-09 16:03:50.003', 3000, 1 ),
( 2, '2013-01-11 16:03:50.003', 5000, 2 ),
( 3, '2013-01-09 16:03:50.003', 2000, 3 ),
( 4, '2013-01-11 16:03:50.003', 1000, 4 ),
( 5, '2013-01-09 16:03:50.003', 4000, 5 ),
( 6, '2013-01-11 16:03:50.003', 6000, 6 ),
( 7, '2013-01-09 16:03:50.003', 7000, 7 ),
( 8, '2013-01-13 16:03:50.003', 9000, 8 );
预期结果是:
Department | Average_Salary
__________________________________
IT | ( X1 + X2 + X3 ) / 3
HR | ( Y1 ) / 1
SubIT | ( X2 + X3 ) / 2
SubSubIT | ( X3 ) / 1
当:
- X1 - IT部门的平均工资
- X2 - 子IT部门的平均工资
- X3 - 孙子IT部门的平均工资
- Y1 - 人力资源部门的平均工资