我该怎么做CTE呢 [英] How do I CTE for this

问题描述

你好CP会员，

这是我的样本表结构，

Hi CP Members,
This is my Sample table Structure,

Create table #table(advId int identity(1,1),name nvarchar(100),ranks nvarchar(5),ReferId int ,ReferalRank nvarchar(5))

insert into #table(name,ranks,ReferId,ReferalRank) values('King','MGR',0,'0')
insert into #table (name,ranks,ReferId,ReferalRank) values('Maceij','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los1','ADV',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Griff','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('SA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('CASSANDRA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Jason','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Smith','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',10,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',10,'MGR')

select *from #table

Let me have words about my Table Structure
 Here ,
 AdvId 1 is Referred by admin ,
  (2,3,4,5) are 1st level of 1
 (6,7,8,9) are 2nd Level of 1
 (10,11) are 3rd level of 1
 (12,13) are 4 th Level of 1
 same Logic  For Each Advisors

喜欢这个结构 [ ^ ]



如何选择经理人数（如何代理商下的许多经理）每个顾问最多3个级别

like this Structure [^]

How do i Select the Count Of Manager(how many manager under the agent) for Each Advisors up to 3 Levels

advId name     CountOfmanager
1     king       8            --2,3,5,6,7,8,9,10
2     Maceij     3            --6,7,10
3      los       2            --8,9
4     Los1       0            -- nobody
5     Griff      0        -- nobody
6     SA         2            -- 10,12
7     CASSANDRA  0            -- nobody
8     Jason      0
9     Smith      0
10    Akee       1             --12
11    manasa     0
12    Akee       0
13    Manasa     0

这就是我所尝试的。

This is What I Tried.

with cte (advId,ReferId,Level)
as
(
 select AdvId,ReferId,1 as Level from table where ReferId=40
 union all
 select a.AdvId,a.ReferId ,Level+1 from table   as a inner join cte as b on b.AdvId=a.ReferId
 )
 select COUNT(b.AdvId From cte as a inner join table as b on a.advId=b.advId where a.level<=3 and b.ranks='MGR' 

我希望它清楚，协助我得到结果



谢谢，

I hope its Clear ,Assist me to get the Result

Thanks,

推荐答案

DECLARE @ID int
DECLARE @cnt int
DECLARE IDs CURSOR LOCAL FOR select advid from #table
Create table #table1(advId int ,name nvarchar(100),cont bigint)
OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN   
with cte (AdvId,ReferId,Level)
as
(
 select AdvId,ReferId,1 as Level from #table where ReferId=@ID
 union all
 select a.AdvId,a.ReferId ,Level + 1 from #table   as a inner join cte as b on b.AdvId=a.ReferId
 )
select @cnt= COUNT(b.AdvId) From cte as a inner join #table as b on a.advId=b.advId where a.level<=3 and b.ranks='MGR'

insert into #table1 select advid,name,'' from #table where advId=@ID
update #table1 set cont=@cnt where advId=@ID


    FETCH NEXT FROM IDs into @ID
END

CLOSE IDs
DEALLOCATE IDs
select * from #table1



Drop table #table1

最简单的方法：

The simplest way:

SELECT advId, name, (SELECT COUNT(*) FROM #table WHERE ReferId =  t1.advId AND ReferalRank = 'MGR') AS CountOfMgr
FROM #table AS t1

对不起，King_Fisher，因为以上查询不符合您的需求。它将员工与其主管直接关系统计;）



这是一个示例，显示整个层次结构;）

Sorry, King_Fisher, because above query does not meets your needs. It counts the employees in direct relationship to its chief ;)

Here is an example, which shows entire hierarchy ;)

;WITH HierarchicalList(EmpName, Hierarchy, EmpId, RefersTo, Lvl)
AS (
    SELECT e.name AS EmpName, CONVERT(NVARCHAR(MAX), e.name) AS Hierarchy, e.advId AS EmpId, CONVERT(NVARCHAR(MAX),'') AS RefersTo, 1 AS Lvl
    FROM #table  AS e
    WHERE e.ReferId = 0
    UNION ALL
    SELECT e.name as EmpName, CONCAT(Hierarchy, '->' , e.name) AS Hierarchy, e.advId  AS EmpID, CONCAT(RefersTo, e.ReferId, ',') AS RefersTo, Lvl + 1
    FROM #table  AS e JOIN HierarchicalList AS d ON e.ReferId  = d.EmpId
    )
SELECT *
FROM HierarchicalList
WHERE Lvl<4
ORDER BY RefersTo

结果：

Result:

EmpName		Hierarchy				EmpId	RefersTo Lvl
King		King					1				1
Maceij		King->Maceij			2	1,			2
Los			King->Los				3	1,			2
Los1		King->Los1				4	1,			2
Griff		King->Griff				5	1,			2
SA			King->Maceij->SA		6	1,2,		3
CASSANDRA	King->Maceij->CASSANDRA	7	1,2,		3
Jason		King->Los->Jason		8	1,3,		3
Smith		King->Los->Smith		9	1,3,		3

我相信你能够根据自己的需要改变它，因为你很聪明人;）



提示：看看 Christian的文章 [ ^ ]关于SQL Wizardry ...。他解释了如何使用CTE编写这样的查询。



干杯，

Maciej

I believe you'll be able to change it to your needs, because you're very smart person ;)

Tip: Have a look at Christian's articles[^] about "SQL Wizardry...". He explains how to write such of query using CTE.

Cheers,
Maciej

谢谢到Maciej Los和Maddy Selva。



问题在StackOverFlow解决，我特此发布解决方案，以从未答复的队列中删除问题，这可能对某人有所帮助。谢谢。:)



http://stackoverflow.com/questions/26669468/how-do-i-use-cte-for-this [ ^ ]

Thank to Maciej Los and Maddy Selva.

The Question is Solved at StackOverFlow and i hereby post the Solution to remove the Question From UnAnswered Queue and this may help somebody. Thank you .:)

http://stackoverflow.com/questions/26669468/how-do-i-use-cte-for-this[^]

这篇关于我该怎么做CTE呢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！