获取资源总数所需的帮助 (SQL Server 2005) [英] Help needed in getting total resource count (SQL Server 2005)

问题描述

我有一个要求.我得到了下表

I have a requirement. I have been given the following table

DownLineid  UplineId    Name    DirectResources
1             2        Sarvesh        7
2            NULL       Admin         5
3             2        Lonesh         10
4             2        Swapna         2
5             2        Priyanka        12
8             2         Sumi            1
6             5         Deepak         10
7             5        Agnijita         6
9             5           Me             1

场景是 UplineId 表示顶级经理，而下线表示在 Upliners 下工作的经理.

The scenario is UplineId means the Top Managers and the Downlines means the Managers working under the Upliners.

我的任务是找出 Upliners 的总资源.所需的输出是

My task is to find out the total resources for the Upliners . The desired output is

UplineId    DownLineid  Name    DirectResources    TotalResources
NULL          2         Admin            5             54
2   3                      Lonesh       10          null
2   5                       Priyanka    12             17
5   7                          Agnijita 6            null
5   6                          Deepak   10           null
5   9                          Me         1            null
2   1                   Sarvesh           7            null
2   8                   Sumi              1            null
2   4                   Swapna            2            null

实际上，管理员是最高的并且拥有所有资源.所以所有资源的总数是 54.

Actually , Admin is the top and is having all the resources . so the total count of all the resources is 54.

Agnijita、Deepak 和 Me 在 Priyanka 之下，此后计数为 17(6+10+1).

Agnijita,Deepak and Me is under Priyanka and henceforth the count is 17(6+10+1).

对于所有其他人，此后没有下线人员在那里为空.

For all other there is no downliners henceforth it is null over there.

到目前为止我的尝试是

declare @t table(DownLineid int,UplineId int,Name varchar(10),DirectResources int)
insert into @t
    select 1,2,'Sarvesh',7 union all select 2,Null,'Admin',5 union all
    select 3,2,'Lonesh',10 union all select 4,2,'Swapna',2 union all
    select 5,2,'Priyanka',12 union all select 8,2,'Sumi',1 union all 
    select 6,5,'Deepak',10 union all select 7,5,'Agnijita',6  union all
    select 9,5,'Me',1
select * from @t
;with cte AS
(

    SELECT 
        CAST(e.Name AS VARCHAR(1000)) AS [Path]
        ,e.UplineId
        ,e.DownLineid
        ,e.Name
        ,0 AS [Level]
        ,e.DirectResources FROM @t e WHERE e.UplineId IS NULL 
    UNION ALL

    SELECT CAST(c.[Path] + '/' + e.Name  AS VARCHAR(1000)) AS [Path]
        ,e.UplineId
        ,e.DownLineid
        ,e.Name
        , c.[Level]+1 AS [Level]
        ,e.DirectResources

    FROM @t e 

    JOIN cte c ON e.UplineId = c.DownLineid

)
select 
UplineId
,DownLineid
, REPLICATE('    ', [Level]) + Name as [Name]
,DirectResources

FROM cte 
ORDER BY [Path]

我正在努力寻找总资源.我使用的是 SQL Server 2005.

I am struggling to find the total resources. I am using SQL Server 2005.

请帮帮我.

提前致谢.

推荐答案

WITH    q AS
        (
        SELECT  downlineID AS parent, downlineID AS id
        FROM    mytable
        UNION ALL
        SELECT  q.parent, m.downlineID AS id
        FROM    q
        JOIN    mytable m
        ON      m.uplineID = q.id
        )
SELECT  q.parent,
        CASE
        WHEN EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.uplineID = q.parent
        )
        THEN SUM(directResources)
        ELSE NULL
        END AS TotalResources
FROM    q
LEFT JOIN
        mytable m
ON      m.downlineID = q.id
GROUP BY
        q.parent

这篇关于获取资源总数所需的帮助 (SQL Server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！