样本数据
DECLARE @CsOrganization TABLE (OrgId int, OrgParentId int, OrgName nvarchar(50));
INSERT INTO @CsOrganization (OrgId, OrgParentId, OrgName) VALUES
(1, NULL, 'X COMPANY'),
(2, 1 , 'Administrator'),
(3, 2 , 'Adm 1'),
(4, 2 , 'Adm 2'),
(5, 3 , 'Adm 1_1');
DECLARE @EmHisOrganization TABLE (EmpId int, OrgId int);
INSERT INTO @EmHisOrganization (EmpId, OrgId) VALUES
(1, 2),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
DECLARE @EmOvertime TABLE (EmpId int, TotalOtReal float);
INSERT INTO @EmOvertime (EmpId, TotalOtReal) VALUES
(1, 1.00),
(2, 2.00),
(3, 3.00),
(4, 2.00),
(5, 1.00);
Query
-
CTE_OrgHours
计算每个组织所有员工的加班时间的简单总和。
-
CTE_Recursive
是遍历组织层次结构的递归 CTE。
- Final
SELECT
对遍历的树进行分组,以求树的每个节点(组织)的小时数总和。
逐步、逐个 CTE 运行此查询并检查中间结果,以更好地了解其工作原理。
WITH
CTE_OrgHours
AS
(
SELECT
Org.OrgId
,Org.OrgParentId
,Org.OrgName
,ISNULL(SUM(Overtime.TotalOtReal), 0) AS SumHours
FROM
@CsOrganization AS Org
LEFT JOIN @EmHisOrganization AS Emp ON Emp.OrgId = Org.OrgID
LEFT JOIN @EmOvertime AS Overtime ON Overtime.EmpId = Emp.EmpId
GROUP BY
Org.OrgId
,Org.OrgParentId
,Org.OrgName
)
,CTE_Recursive
AS
(
SELECT
CTE_OrgHours.OrgId
,CTE_OrgHours.OrgParentId
,CTE_OrgHours.OrgName
,CTE_OrgHours.SumHours
,1 AS Lvl
,CTE_OrgHours.OrgId AS StartOrgId
,CTE_OrgHours.OrgName AS StartOrgName
FROM CTE_OrgHours
UNION ALL
SELECT
CTE_OrgHours.OrgId
,CTE_OrgHours.OrgParentId
,CTE_OrgHours.OrgName
,CTE_OrgHours.SumHours
,CTE_Recursive.Lvl + 1 AS Lvl
,CTE_Recursive.StartOrgId
,CTE_Recursive.StartOrgName
FROM
CTE_OrgHours
INNER JOIN CTE_Recursive ON CTE_Recursive.OrgId = CTE_OrgHours.OrgParentId
)
SELECT
StartOrgId
,StartOrgName
,SUM(SumHours) AS TotalHours
FROM CTE_Recursive
GROUP BY
StartOrgId
,StartOrgName
ORDER BY StartOrgId;
Result
+------------+---------------+------------+
| StartOrgId | StartOrgName | TotalHours |
+------------+---------------+------------+
| 1 | X COMPANY | 9 |
| 2 | Administrator | 9 |
| 3 | Adm 1 | 4 |
| 4 | Adm 2 | 2 |
| 5 | Adm 1_1 | 1 |
+------------+---------------+------------+