如何在SQL中创建含机构祖孙三代关系及子孙数量的视图

原始表结构 #

现有表（表名假设为Agency）结构如下：

需求说明 #

需要开发SQL Server数据库视图AgencyView，展示每个机构的父级、祖父级信息，同时统计其子级和孙级数量。视图必须包含以下字段：

• GrandParentAgencyNo：祖父级机构ID
• GrandParentName：祖父级机构名称
• ParentAgencyNo：父级机构ID
• ParentName：父级机构名称
• AgencyNo：当前机构ID
• AgencyName：当前机构名称
• NumberOfChildren：当前机构的直接子级数量
• NumberOfGrandChildren：当前机构的孙级数量

特殊情况处理 #

• 独立机构（无父级）：父级和祖父级均指向自身，子级、孙级数量显示0（结果示例见下文）
• 仅有父级无祖父级的机构：祖父级指向父级机构

视图查询示例 #

可通过父级ID查询所有子级，示例语句：

SELECT * FROM AgencyView WHERE ParentAgencyNo = 'ABC123'

独立机构在视图中的结果示例：

- GrandParentAgencyNo: 1 
- GrandParentName: ABC机构 
- ParentAgencyNo: 1
- ParentName: ABC机构 
- AgencyNo: 1
- AgencyName: ABC机构 
- NumberOfChildren: 0
- NumberOfGrandChildren: 0

遇到的问题 #

尝试编写递归CTE查询祖父级信息时，频繁触发SQL Server最大递归次数限制，现有尝试代码如下：

WITH A(Id, ParentId) AS 
(
    SELECT AgencyId, ParentAgencyId FROM Agency 
    UNION ALL
    SELECT e.AgencyId, p.ParentId FROM Agency e 
    JOIN A p ON e.ParentAgencyId = p.Id
)
SELECT * FROM A
OPTION (MAXRECURSION 32767)

解决方案 #

以下是满足需求的视图创建语句，通过两次自连接获取父级、祖父级，结合子查询统计子级和孙级数量，无需递归即可避免递归限制问题：

CREATE VIEW AgencyView
AS
WITH ChildCounts AS (
    -- 统计每个机构的直接子级数量
    SELECT 
        ParentID AS AgencyID,
        COUNT(*) AS NumberOfChildren
    FROM Agency
    WHERE ParentID IS NOT NULL
    GROUP BY ParentID
),
GrandChildCounts AS (
    -- 统计每个机构的孙级数量：子级的子级
    SELECT 
        a.ParentID AS AgencyID,
        COUNT(*) AS NumberOfGrandChildren
    FROM Agency a
    JOIN Agency b ON a.AgencyID = b.ParentID
    JOIN Agency c ON b.AgencyID = c.ParentID
    GROUP BY a.ParentID
)
SELECT 
    -- 处理祖父级：若无祖父级则取父级，若无父级则取自身
    ISNULL(gp.AgencyID, ISNULL(p.AgencyID, a.AgencyID)) AS GrandParentAgencyNo,
    ISNULL(gp.CompanyName, ISNULL(p.CompanyName, a.CompanyName)) AS GrandParentName,
    -- 处理父级：若无父级则取自身
    ISNULL(p.AgencyID, a.AgencyID) AS ParentAgencyNo,
    ISNULL(p.CompanyName, a.CompanyName) AS ParentName,
    a.AgencyID AS AgencyNo,
    a.CompanyName AS AgencyName,
    ISNULL(cc.NumberOfChildren, 0) AS NumberOfChildren,
    ISNULL(gcc.NumberOfGrandChildren, 0) AS NumberOfGrandChildren
FROM Agency a
-- 左连接父级机构
LEFT JOIN Agency p ON a.ParentID = p.AgencyID
-- 左连接祖父级机构（父级的父级）
LEFT JOIN Agency gp ON p.ParentID = gp.AgencyID
-- 左连接子级统计结果
LEFT JOIN ChildCounts cc ON a.AgencyID = cc.AgencyID
-- 左连接孙级统计结果
LEFT JOIN GrandChildCounts gcc ON a.AgencyID = gcc.AgencyID

代码说明 #

1. ChildCounts CTE：通过分组父ID，统计每个机构的直接子级数量。
2. GrandChildCounts CTE：通过三次表连接（当前机构→子级→孙级），分组统计每个机构的孙级数量。
3. 主查询：
   • 用LEFT JOIN关联父级、祖父级机构，通过ISNULL处理无父/祖父级的特殊情况。
   • 用LEFT JOIN关联子级、孙级统计结果，无对应数据时显示0。

内容的提问来源于stack exchange，提问作者R. Hayes