获取资源总数所需的帮助 (SQL Server 2005)

本文介绍了获取资源总数所需的帮助 (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