我该怎么做CTE呢

本文介绍了我该怎么做CTE呢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

你好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解决，我特此发布解决方案，以从未答复的队列中删除问题，这可能对某人有所帮助。谢谢。:) stackoverflow/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 .:) stackoverflow/questions/26669468/how-do-i-use-cte-for-this[^]