CTE计算层级关系

推广渠道表有ParentID字段，代表上下层级关系。现要统计每个推广员，推广了多少人？

--创建表结构，插入测试数据
USE DBA_Monitor
GO
CREATE TABLE [dbo].[TGYinfo](
    [TGYID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [ParentID] [int] NULL,
    [CreateTime] [datetime] NULL,
 CONSTRAINT [PK_tgyTbl] PRIMARY KEY CLUSTERED 
(
    [TGYID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TGYinfo] ADD  CONSTRAINT [DF_TGYinfo_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
SET IDENTITY_INSERT [dbo].[TGYinfo] ON
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (1, N'推广1', 0, CAST(0x0000A4D100182B20 AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (2, N'推广2', 0, CAST(0x0000A4D100182B21 AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (3, N'推广1_1', 1, CAST(0x0000A4D100185C2E AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (4, N'推广1_2', 1, CAST(0x0000A4D100185C2F AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (5, N'推广1_3', 1, CAST(0x0000A4D100185C3D AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (6, N'推广1_1_1', 3, CAST(0x0000A4D10021A746 AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (7, N'推广1_1_2', 3, CAST(0x0000A4D100A1132A AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (8, N'推广2_1', 2, CAST(0x0000A4D100A13AE0 AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (9, N'推广2_2', 2, CAST(0x0000A4D100A1428E AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (10, N'推广2_1_1', 8, CAST(0x0000A4D100A15D3C AS DateTime))
INSERT [dbo].[TGYinfo] ([TGYID], [Name], [ParentID], [CreateTime]) VALUES (11, N'推广2_1_2', 8, CAST(0x0000A4D100A161F9 AS DateTime))
SET IDENTITY_INSERT [dbo].[TGYinfo] OFF

原始数据如下图所示，需要统计每个节点有多少下层用户

网上很多针对单一节点返回所有下层关系的代码，参考修改运行结果能正常返回。试想如果能将每次的单一节点一并返回出来，问题就基本解决了。或者说用一个字段标识，某N行对应是节点N返回的结果

--单一节点返回所有下层关系的代码
;with cte as
(select TGYID,Name,ParentID from TGYinfo
 WHERE TGYID=1--单一节点
union all
select a.TGYID,a.Name,a.ParentID
from TGYinfo a
inner join cte c
on c.TGYID=a.ParentID
)
select * from cte

增加OrderID字段，区分数据由哪个节点返回

--生成OrderID字段
;with cte as
(select TGYID,Name,ParentID,convert(bigint,1000+TGYID) as OrderID--每层用四位编码
from TGYinfo
union all
select a.TGYID,a.Name,a.ParentID,convert(bigint,c.OrderID*10000)+convert(bigint,a.TGYID) OrderID
from TGYinfo a
inner join cte c
on c.TGYID=a.ParentID
)
select * from cte
order by convert(varchar,OrderID)

根据OrderID统计各节点的下层用户数

--最终统计结果
;with cte as
(select TGYID,Name,ParentID,convert(bigint,1000+TGYID) as OrderID--每层用四位编码
from TGYinfo
union all
select a.TGYID,a.Name,a.ParentID,convert(bigint,c.OrderID*10000)+convert(bigint,a.TGYID) OrderID
from TGYinfo a
inner join cte c
on c.TGYID=a.ParentID
)
select b.*,a.TGCount from(
select left(OrderID,4)%1000 TGYID,count(*) TGCount from cte
group by left(OrderID,4)
) a
inner join TGYinfo b
on a.TGYID=b.TGYID
order by a.TGYID

上面这个语句适合表中记录数<10000，如果有多的话，对应代码中的数值要扩大