如果您对一个特定的日期感兴趣，这很简单。
看来您需要将
WHERe过滤器移至查询的较早部分。进入
CTE_OrgHours。
CTE_OrgHours每个组织应返回一行，其中包含相关时间的总和。所有过滤都应在此查询中进行。递归部分以后希望每个组织中有一行
CTE_OrgHours。
WITHCTE_OrgHoursAS( 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 AND Overtime.AttdDate = '2016-05-12' GROUP BY Org.OrgId ,Org.OrgParentId ,Org.OrgName),CTE_RecursiveAS( 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 TotalHoursFROM CTE_RecursiveGROUP BY StartOrgId ,StartOrgNameORDER BY StartOrgId;
