按经理递归汇总调查结果

Question

我有一个 StaffLookup 表，如下所示。

UserSrn | UserName | ManagerSrn
===============================
ABC1    | Jerome   | NULL
ABC2    | Joe      | ABC1
ABC3    | Paul     | ABC2
ABC4    | Jack     | ABC3
ABC5    | Daniel   | ABC3
ABC6    | David    | ABC2
ABC7    | Ian      | ABC6
ABC8    | Helen    | ABC6

人员结构是这样的。

|- Jerome
 |
 |- Joe
 ||
 ||- Paul
 |||
 |||- Jack
 |||
 |||- Daniel
 ||
 ||- David
 |||
 |||- Ian
 |||
 |||- Helen

我有一个如下所示的调查回复列表。

UserSrn | QuestionId | ResponseScore
====================================
ABC2    | 1          | 5
ABC2    | 3          | 4
ABC4    | 16         | 3
...

我想做的事情听起来很简单，但我正在努力寻找一种简洁、快速的方法。我想创建一个存储过程，它接受一个 Srn 并返回结构中该 Srn 下的所有员工。

如果 QuestionId 的得分为 16，则表明调查已完成。我想返回输入的 Srn（最高管理者）的一行，以及该经理下属的直接下属已完成的调查的计数。在此基础上，我希望原经理下的每位经理都可以统计他们每个直接下属的已完成调查的数量，等等。

当我将最高管理者设置为 Joe (ABC2) 时，我希望看到如下数据。

UserName | Completed | Total
============================
Joe      | 2         | 2
Paul     | 1         | 2
David    | 0         | 2
TOTAL    | 3         | 6

Answer 1

根据所提供的信息，我相信这是有效的。将其转换为以 @mgrSrn 作为输入参数的 SP 应该不会太困难。

declare @users table
(UserSrn char(4)
,UserName varchar(6)
,ManagerSrn char(4)
)

INSERT @users
      SELECT 'ABC1','Jerome',NULL
UNION SELECT 'ABC2','Joe','ABC1'
UNION SELECT 'ABC3','Paul','ABC2'
UNION SELECT 'ABC4','Jack','ABC3'
UNION SELECT 'ABC5','Daniel','ABC3'
UNION SELECT 'ABC6','David','ABC2'
UNION SELECT 'ABC7','Ian','ABC6'
UNION SELECT 'ABC8','Helen','ABC6'

declare @results table
(UserSrn char(4)
,QuestionId tinyint
,ResponseScore tinyint
)

INSERT @results
      SELECT 'ABC2',1,1
UNION SELECT 'ABC4',16,1

declare @mgrSrn char(4)
set @mgrSrn = 'ABC2' -- Joe


;WITH completedCTE
AS
(
    SELECT c.*
           ,CASE WHEN r.UserSrn IS NOT NULL
                 THEN 1
                 ELSE 0
            END     AS completeCount
           ,1       AS totalCount
    FROM      @users as c
    LEFT JOIN @results AS r
    ON        r.UserSrn    = c.UserSrn
    AND       r.QuestionId = 16
)
,recCTE
AS
(
    SELECT  UserSrn
            ,UserName
            ,CAST(NULL AS CHAR(4)) AS ManagerSrn
            ,1 as level
            ,completeCount 
            ,totalCount
    FROM completedCTE
    WHERE UserSrn = @mgrSrn

    UNION ALL

    SELECT t.UserSrn
           ,t.UserName
           ,t.ManagerSrn
           ,c.level + 1 AS level
           ,t.completeCount AS completeCount
           ,t.totalCount AS totalCount
    FROM completedCTE AS t
    JOIN recCTE AS c
    ON   c.UserSrn = t.ManagerSrn


)
,resultCTE
AS
(
    SELECT r.ManagerSrn
           ,t.UserName
           ,r.level
           ,SUM(completeCount) completeCount
           ,SUM(totalCount)    totalCount
    FROM recCTE AS r
    JOIN @users     AS t
    ON  t.UserSrn = r.ManagerSrn
    WHERE r.ManagerSrn IS NOT NULL
    GROUP BY r.ManagerSrn
             ,t.UserName 
             ,r.level

)
SELECT UserName
       ,completeCount
       ,totalCount
FROM resultCTE  
ORDER BY level
         ,UserName   
OPTION (MAXRECURSION 0) 

Answer 2

试试这个：

DECLARE @Staff table (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @Staff VALUES ('ABC1','Jerome', NULL )
INSERT @Staff VALUES ('ABC2','Joe'   ,'ABC1')
INSERT @Staff VALUES ('ABC3','Paul'  ,'ABC2')
INSERT @Staff VALUES ('ABC4','Jack'  ,'ABC3')
INSERT @Staff VALUES ('ABC5','Daniel','ABC3')
INSERT @Staff VALUES ('ABC6','David' ,'ABC2')
INSERT @Staff VALUES ('ABC7','Ian'   ,'ABC6')
INSERT @Staff VALUES ('ABC8','Helen' ,'ABC6')

DECLARE @SurveyResponses table (UserSrn char(4), QuestionId int, ResponseScore int)
INSERT @SurveyResponses VALUES ('ABC2',1 ,5)
INSERT @SurveyResponses VALUES ('ABC2',3 ,4)
INSERT @SurveyResponses VALUES ('ABC6',16,3)

DECLARE @RootUserSrn  char(4)
SET @RootUserSrn='ABC2'

--get tree of given user
;WITH StaffTree AS
(
    SELECT 
        UserSrn, UserName, ManagerSrn, UserSrn AS ManagerUserSrn, UserName AS ManagerUserName, 1 AS LevelOf
        FROM @Staff
        WHERE UserSrn=@RootUserSrn
    UNION ALL
        SELECT 
            s.UserSrn, s.UserName, s.ManagerSrn, t.UserSrn, t.UserName, t.LevelOf+1
        FROM StaffTree         t
            INNER JOIN @Staff  s ON t.UserSrn=s.ManagerSrn
        WHERE s.ManagerSrn=@RootUserSrn

)
SELECT 
    s.UserName,COUNT(r.QuestionId) AS Completed,'???' as total

    FROM StaffTree                        s
        LEFT OUTER JOIN @SurveyResponses  r ON s.UserSrn=r.UserSrn
    GROUP BY s.UserName,s.LevelOf
    ORDER BY s.LevelOf

输出：

UserName   Completed   total
---------- ----------- -----
Joe        2           ???
David      1           ???
Paul       0           ???

编辑在OP的评论之后：

DECLARE @Staff table (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @Staff VALUES ('ABC1','Jerome', NULL )
INSERT @Staff VALUES ('ABC2','Joe'   ,'ABC1')
INSERT @Staff VALUES ('ABC3','Paul'  ,'ABC2')
INSERT @Staff VALUES ('ABC4','Jack'  ,'ABC3')
INSERT @Staff VALUES ('ABC5','Daniel','ABC3')
INSERT @Staff VALUES ('ABC6','David' ,'ABC2')
INSERT @Staff VALUES ('ABC7','Ian'   ,'ABC6')
INSERT @Staff VALUES ('ABC8','Helen' ,'ABC6')

DECLARE @SurveyResponses table (UserSrn char(4), QuestionId int, ResponseScore int)
INSERT @SurveyResponses VALUES ('ABC2',1 ,5)
INSERT @SurveyResponses VALUES ('ABC2',3 ,4)
INSERT @SurveyResponses VALUES ('ABC6',16,3)

DECLARE @RootUserSrn  char(4)
SET @RootUserSrn='ABC2'

--get tree of given user
;WITH StaffTree AS
(
    SELECT 
        UserSrn, UserName, ManagerSrn, UserSrn AS ManagerUserSrn, UserName AS ManagerUserName, 1 AS LevelOf
        FROM @Staff
        WHERE UserSrn=@RootUserSrn
    UNION ALL
        SELECT 
            s.UserSrn, s.UserName, s.ManagerSrn, t.UserSrn, t.UserName, t.LevelOf+1
        FROM StaffTree         t
            INNER JOIN @Staff  s ON t.UserSrn=s.ManagerSrn
        WHERE s.ManagerSrn=@RootUserSrn

)
, MINLevel AS (
    SELECT MIN(LevelOf) AS MinLevelOf FROM StaffTree
)
, TotalLevel AS (
    SELECT
        SUM(CASE WHEN s.LevelOf !=m.MinLevelOf THEN 1 ELSE 0 END) AS TotalOf
        FROM StaffTree            s
            CROSS JOIN MINLevel   m
)
,Results AS (
    SELECT 
        s.UserName,SUM(CASE WHEN r.QuestionId=16 THEN 1 ELSE 0 END) AS Completed,t.TotalOf as total,s.LevelOf

        FROM StaffTree                        s
            LEFT OUTER JOIN @SurveyResponses  r ON s.UserSrn=r.UserSrn
            CROSS JOIN TotalLevel             t
        GROUP BY s.UserName,s.LevelOf,t.TotalOf
)
SELECT
    UserName,Completed,total, 1,LevelOf
    FROM Results
UNION ALL
    SELECT
        'TOTAL',SUM(Completed),SUM(total),2,0
        FROM Results
ORDER BY 4,5

输出：

UserName   Completed   total                   LevelOf
---------- ----------- ----------- ----------- -----------
Joe        0           2           1           1
David      1           2           1           2
Paul       0           2           1           2
TOTAL      1           6           2           0

(4 row(s) affected)

我仍然看不到给定的数据如何导致乔完成=2和保罗完成1。我从('ABC4',16,3) 到 ('ABC6',16,3)，这样结果集中的某个人就会完成一个。

Answer 3

编辑：我使用 SQL Server 2008 生成 INSERT 语句...

我可以生成您的层次结构，但不能生成结果。抱歉，示例输入和输出数据没有关联。

您需要 LevelNum 将最有可能与层次结构相关的结果联系起来

DECLARE @staff TABLE (UserSrn char(4), UserName varchar(10), ManagerSrn char(4))
INSERT @staff (UserSrn, UserName, ManagerSrn)
VALUES 
('ABC1'    , 'Jerome'   , NULL),('ABC2'    , 'Joe'      , 'ABC1'),
('ABC3'    , 'Paul'     , 'ABC2'),('ABC4'    , 'Jack'     , 'ABC3'),
('ABC5'    , 'Daniel'   , 'ABC3'),('ABC6'    , 'David'    , 'ABC2'),
('ABC7'    , 'Ian'      , 'ABC6'),('ABC8'    , 'Helen'    , 'ABC6')

DECLARE @results TABLE (UserSrn char(4), QuestionId varchar(10), ResponseScore char(4))
INSERT @results (UserSrn, QuestionId, ResponseScore)
VALUES ('ABC2'    , 2   , 5),('ABC2'    , 3      , 4),('ABC4'    , 16     , 3)

;WITH cHierarchy AS
(
    SELECT
       s.UserSrn, S.UserName, S.ManagerSrn, CAST('|' AS varchar(50)) AS LevelStr, 0 AS LevelNum
    FROM
       @staff S
    WHERE
       S.ManagerSrn IS NULL
    UNION ALL
    SELECT
       s.UserSrn, S.UserName, S.ManagerSrn, CAST(Level + '|' AS varchar(50)), LevelNum + 1
    FROM
       cHierarchy C JOIN @staff S ON C.UserSrn = S.ManagerSrn
)
SELECT
    *
FROM
    cHierarchy C