代码之家 › 专栏 › 技术社区 › priyanka.sarkar

获取总资源计数时需要的帮助(SQL Server 2005)

tsql sql-server-2005 sql-server

1

priyanka.sarkar · 技术社区 · 16 年前

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    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

阿格尼吉塔、迪帕克和我都在普里扬卡手下,从那时起,人数是17(6+10+1)。

对于所有其他的下行链路,从那时起,它在那里是空的。

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]

我正在努力寻找全部资源。我正在使用SQLServer2005。

请帮帮我。

2 回复 | 直到 16 年前

1

2

Quassnoi 16 年前

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

2

0

ram 16 年前

这是我很久以前在我的书里写的 blog

with Manager AS (

select  EmpID,ManID from EmpMan where ManID=1/* assuming that your VP ID is 1, or it can be the top most person whom you want to query on*/

union all

select E.EmpID,E.ManID from EmpMan E

join Manager M on  E.ManID=M.EmpID)

select * from Manager