具有汇总逻辑的递归sql函数? [英] recursive sql function with rollup logic?

问题描述

我有一个 SQL，它使用递归 CTE 扩展自引用员工表，构建了按用户和严重性级别聚合的缺陷结果集.

i have a SQL that using a recursive CTE to expand a self-referancing employees table builds a result set of defects aggregated by user and severity level.

这是我的 CTE:

    ALTER FUNCTION [dbo].[fnGetEmployeeHierarchyByUsername] 
(    
  @NTID varchar(100) = null
)  
RETURNS TABLE  
AS  
RETURN  
(  
  WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerNTID, ManagerID, NTID, FullName--, Name  
    FROM Employees  
    WHERE NTID = @NTID
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerNTID, e.ManagerID, e.NTID, e.FullName--, e.Name  
    FROM Employees e  
    JOIN yourcte y ON e.ManagerNTID = y.NTID
  )  
SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
FROM yourcte  
)

这是我的用户聚合缺陷的 SQL:

here is my SQL for aggregating defects by the user:

SELECT e.FullName, Urgent, High, Medium, Low
FROM fnGetEmployeeHierarchyByUsername ('ssalvati') e
LEFT OUTER JOIN(
    SELECT [AssignedTo],
           SUM([1-Urgent]) AS Urgent,
           SUM([2-High]) AS High,
           SUM([3-Medium]) AS Medium,
           SUM([4-Low]) AS Low
      FROM (SELECT [AssignedTo],[BusinessSeverity] FROM Defects WHERE Status <> 'Closed') D
     PIVOT (COUNT([BusinessSeverity]) FOR [BusinessSeverity] IN ([1-Urgent],[2-High],[3-Medium],[4-Low])) V
    GROUP BY [AssignedTo]) AS def
ON e.ntid = def.[AssignedTo]

我想要一个以用户名作为参数的 porc 并生成一个类似于上面的 SQL 的结果，但有 2 个增强:

i want to have a porc that takes a username as a param and generates a result like the SQL above but with 2 enhancements:

1. 我需要它列出作为参数传入的用户，以作为结果集的第一条记录列出.

1. i need it to list the user passed in as a param to be listed as the first record of the result-set.

我需要向经理报告的员工只显示一层深度，而不是显示完整的树.第一级应该是所有底层缺陷的汇总，这些缺陷分配给汇总为所有一级用户的人员.换句话说，我不想像现在这样在管理器下显示一整棵树，我需要它只显示一个深度，但所有级别的缺陷总和.

i need the employees that report into the manager to show only one level deep and not show the full tree. the first level should be a roll up of all the underlying defects assigned to people who roll up into all the level one users. in other words i dont want to show a entire tree under the manager like it is now, i need it to show only one level deep but with a sum of defects for all the levels.

想法?

推荐答案

这里有一个很长的虚拟方法.我有它的工作，但解决方案可能会更好.我希望有人会发布一个 SQL2005 方法来完成这项工作...

here is the long dummy way of doing it. i have it working but the solution could be much better. i am hoping someone will post a SQL2005 way of getting this done...

    alter PROC sel_DefectReportByManagerNTID_rollup
(@ManagerNTID NVARCHAR(100))    
AS

CREATE TABLE #DefectCounts
(
id INT IDENTITY(1, 1) ,
MgrRolledInto NVARCHAR(100) NULL,
AltBusinessSeverity NVARCHAR(100) NULL,
DefectCount INT NULL
);


CREATE TABLE #directReports
(
pk INT IDENTITY(1, 1) ,
directReportNTID NVARCHAR(100) NULL
);

INSERT INTO #directReports
SELECT NTID FROM Employees WHERE ManagerNTID = @ManagerNTID
--select * from #directReports

DECLARE @maxPK INT;
SELECT @maxPK = MAX(PK) FROM #directReports

DECLARE @pk INT;
SET @pk = 1


INSERT INTO #DefectCounts (MgrRolledInto,AltBusinessSeverity,DefectCount)
SELECT @ManagerNTID, d.AltBusinessSeverity, COUNT(*)
        FROM Defects d
            JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10
        WHERE d.AssignedTo = @ManagerNTID
        GROUP BY d.AltBusinessSeverity


WHILE @pk <= @maxPK
BEGIN
    /* Get one direct report at a time to aggregate their defects under them... */
    DECLARE @dirRptNTID NVARCHAR(100);
    SET @dirRptNTID = (SELECT directReportNTID
                        FROM #directReports
                        WHERE PK = @pk)


    INSERT INTO #DefectCounts (MgrRolledInto,AltBusinessSeverity,DefectCount)
        SELECT @dirRptNTID, d.AltBusinessSeverity, COUNT(*)
        FROM Defects d
            JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10
            JOIN (SELECT * FROM fnGetEmployeeHierarchyByUsername(@dirRptNTID) ) emp ON emp.NTID = d.AssignedTo
        WHERE d.AssignedTo IS NOT NULL
        GROUP BY d.AltBusinessSeverity

    SELECT @pk = @pk + 1
END



SELECT  e.FullName,     
  isnull(Urgent,0) as Urgent,     
  isnull(High,0) as High,     
  isnull(Medium,0) as Medium,    
  isnull(Medium3000,0) as Medium3000,    
  isnull(Low,0) as Low    
FROM (  select * from fnGetEmployeeHierarchyByUsername (@ManagerNTID) where depth <= 1) e    
left outer join (
                    SELECT  MgrRolledInto,    
                            SUM([1-Urgent]) AS Urgent,    
                            SUM([2-High]) AS High,    
                            SUM([3-Medium]) AS Medium,    
                            SUM([3-Medium (3000)]) AS Medium3000,  
                            SUM([4-Low]) AS Low    
                    FROM #DefectCounts dfs
                    PIVOT 
                    (sum(DefectCount) FOR AltBusinessSeverity IN ([1-Urgent],[2-High],[3-Medium],[3-Medium (3000)],[4-Low])) V    
                    GROUP BY MgrRolledInto
                ) def_data on def_data.MgrRolledInto = e.NTID
order by e.depth

这篇关于具有汇总逻辑的递归sql函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！