我该怎么做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屋!