我有一个表,其中的数据分层存储在名为 CsOrganization 的表中,如下表所示。
Table Name : CsOrganization
OrgId OrgParentId OrgName
1 NULL X COMPANY
2 1 Administrator
3 2 Adm 1
4 2 Adm 2
5 3 Adm 1_1
然后有一个名为 EmHisOrganization 的表,它与 CsOrganization 表相关,如下表所示。
Table Name : EmHisOrganization
EmpId OrgId
1 2
2 2
3 3
4 4
5 5
每个员工都将拥有基于他们所在组织的加类数据,并将其存储在 EmOvertime 表中。
Table Name : EmOvertime
EmpId TotalOtReal
1 1.00
2 2.00
3 3.00
4 2.00
5 1.00
问题是我需要根据每个组织获得 TotalHours 的总和。 TotalHours 的总和还必须对其所有子项的 TotalHours 数据求和。 到目前为止,我设法找出他们的 parent 和 child ,但我无法弄清楚如何从不同的表中获取 TotalHours 数据并对其进行求和。据我所知,我需要加入这些表才能获得 TotalHours,但不幸的是,CTE Recursive 不允许在语法中使用 OUTER JOIN。这是我想要的基于上述示例的输出:
Desired Output
OrgId OrgName TotalHours
1 X COMPANY 9.00
2 Administrator 9.00
3 Adm 1 4.00
4 Adm 2 2.00
5 Adm 1_1 1.00
请注意,Adm 1 的 TotalHours 来自 ID 为 3 的总和,其在 TotalHours 列中的值为 3.00,而 ID 为 5 的员工在 TotalHours 列中的值为 1.00,结果在所需表中为 4.00。当 OrgId 的 ID 为 1 和 2 的 TotalHours 中的值为 9.00 时,情况也是如此。
任何帮助将不胜感激。
于 09/05/2016, 12.02 编辑,添加了表和我的查询尝试的关系。
这是关系的外观: Table Relationship 。
我的查询尝试(这些结果在每个组织上为 0.00,但如果不推荐使用 where 子句,则 anchor 查询显示正确的值):
With OrgTree (OrgId, OrgName, TotalHours) AS
(
SELECT orgId, orgN, SUM(eReal) AS TotalHours
FROM (SELECT OrgId AS orgId, OrgName AS orgN, CASE WHEN x.TotalOtReal IS NULL THEN 0 ELSE x.TotalOtReal END AS eReal
FROM (SELECT f.OrgId, f.OrgName, o.TotalOtReal
FROM dbo.CsOrganization AS f LEFT OUTER JOIN
(SELECT OrgId, SUM(TotalOtReal) AS TotalOtReal
FROM (SELECT a.EmpId, a.OrgId, b.TotalOtReal
FROM dbo.EmHisOrganization AS a LEFT OUTER JOIN
(SELECT EmpId, SUM(TotalOtReal) AS TotalOtReal
FROM dbo.EmOvertime AS a
GROUP BY EmpId) AS b ON a.EmpId = b.EmpId) AS a_1
GROUP BY OrgId) AS o ON f.OrgId = o.OrgId
) AS x WHERE OrgId = 1) AS xx
GROUP BY orgId, orgN
UNION ALL
SELECT a.OrgId, a.OrgName, TotalHours FROM dbo.CsOrganization a
INNER JOIN OrgTree o ON a.OrgParentId = o.OrgId
)
SELECT a.OrgId, a.OrgName, SUM(a.TotalHours) AS TotalHours FROM OrgTree a
GROUP BY a.OrgId, a.OrgName
最佳答案
样本数据
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);
查询
CTE_OrgHours
计算每个组织所有员工的加类时间的简单总和。 CTE_Recursive
是遍历组织层次结构的递归 CTE。 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;
结果
+------------+---------------+------------+
| StartOrgId | StartOrgName | TotalHours |
+------------+---------------+------------+
| 1 | X COMPANY | 9 |
| 2 | Administrator | 9 |
| 3 | Adm 1 | 4 |
| 4 | Adm 2 | 2 |
| 5 | Adm 1_1 | 1 |
+------------+---------------+------------+
关于SQL Server - 来自不同表的 CTE 递归 SUM 值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37107678/