怎样用SQL递归求各个Folder的文件数

-- 数据准备
CREATE TABLE [dbo].[ContentObject] (Id NCHAR(10), Name NVARCHAR(500),ParentId NCHAR(10),Type NVARCHAR(50));
INSERT INTO [dbo].[ContentObject] VALUES('0x00000001', 'ROOT', NULL, 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000011', '_Admin', '0x00000001', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000012', 'Chris', '0x00000001', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000013', 'Fund Story', '0x00000001', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000014', 'Pitchbook', '0x00000001', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000015', 'SQL Server 2008', '0x00000001', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000016', 'Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions', '0x00000001', 'Content');

INSERT INTO [dbo].[ContentObject] VALUES('0x00000017', '_DataSource', '0x00000011', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000018', '_Report', '0x00000011', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000019', 'Content Inventory Report', '0x00000018', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000020', 'User Inventory Report', '0x00000018', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000021', 'Content Data', '0x00000017', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000022', 'User Data', '0x00000017', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000023', 'System Report', '0x00000011', 'Content');

INSERT INTO [dbo].[ContentObject] VALUES('0x00000024', 'Oracle', '0x00000012', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000025', 'Power BI', '0x00000012', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000026', 'PDF', '0x00000012', 'Folder');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000027', 'Oracle PL SQL从入门到精通_丁士锋', '0x00000024', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000028', 'Oracle优化日记：一个金牌DBA的故事', '0x00000024', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000029', 'Oracle核心技术', '0x00000024', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000030', 'Oracle查询优化改写  技巧与案例', '0x00000024', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000031', 'SQL解惑', '0x00000024', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000032', '微软Excel 2013 用PowerPivot 建立数据模型 Building Data Models with PowerPivot', '0x00000025', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000033', '别怕，EXCEL VBA其实很简单', '0x00000025', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000034', '数据结构  Visual Basic版', '0x00000025', 'Content');

INSERT INTO [dbo].[ContentObject] VALUES('0x00000035', 'HTML & CSS Design and Build Websites', '0x00000014', 'Content');
INSERT INTO [dbo].[ContentObject] VALUES('0x00000036', 'JavaScript & jQuery交互式Web前端开发', '0x00000014', 'Content');

--1. 只求末枝子 Folder 的隶属关系
WITH parentNode AS (
SELECT co.ParentId
     , COUNT(1) Cts
  FROM [dbo].[ContentObject] co
 WHERE co.Type='Folder'
       AND co.ParentId IS NOT NULL
 GROUP BY co.ParentId
)
, TEMP AS (
--本层获取全部末枝 Folder
SELECT co.Id FolderId
     , co.Name FolderName
     , co.ParentId
  FROM [dbo].[ContentObject] co
       LEFT JOIN parentNode pn ON co.Id=pn.ParentId
 WHERE co.Type='Folder'
       AND pn.ParentId IS NULL
)
, FullPath AS (
SELECT t.FolderId
     , t.FolderName
     , CONVERT(NVARCHAR(MAX), t.FolderName) PathNameP
     , t.FolderId ParentNode
     , t.ParentId
  FROM TEMP t
UNION ALL
SELECT t.FolderId
     , t.FolderName
     , CONVERT(NVARCHAR(MAX),co.Name+'/'+t.PathNameP)
     , t.ParentId ParentNode
     , co.ParentId
  FROM [dbo].[ContentObject] co
       INNER JOIN FullPath t ON t.ParentId=co.Id
 WHERE co.Type='Folder'
)
SELECT fp.FolderId
     , fp.FolderName
     , fp.PathNameP
     , fp.ParentNode
  FROM FullPath fp
 ORDER BY FolderName

--2. 求各个分枝子 Folder 的隶属关系
--PS: 该示例用于递归求各个Folder的文件数
WITH FullPath AS (
SELECT co.Id FolderId
     , co.Name FolderName
     , CONVERT(NVARCHAR(MAX), co.Name) NodeName
     , CONVERT(NVARCHAR(MAX), co.Name) FolderPath
     , co.Id ParentNode
     , co.ParentId
     , 1 Level
  FROM [dbo].[ContentObject] co
 WHERE co.Type='Folder'
UNION ALL
SELECT t.FolderId
     , t.FolderName
     , CONVERT(NVARCHAR(MAX), co.Name) NodeName
     , CONVERT(NVARCHAR(MAX),co.Name+'/'+t.NodeName) FolderPath
     , co.Id ParentNode
     , co.ParentId
     , Level+1
  FROM [dbo].[ContentObject] co
       INNER JOIN FullPath t ON t.ParentId=co.Id
 WHERE co.Type='Folder'
)
SELECT FolderId
     , fp.FolderName
     , fp.NodeName
     , fp.FolderPath
     , fp.ParentNode
     , fp.ParentId
  FROM FullPath fp
 ORDER BY fp.FolderName, fp.Level