如何通过PK/FK查询层级组织的自身、父级及祖父级名称？

表结构确认 #

• Organization（组织表）
  • OrgPK：组织唯一标识（主键）
  • ParentOrgPK：父组织标识（外键，关联自身OrgPK）
  • OrgName：组织名称
• personOrganization（人员-组织关联表）
  • OrgFK：关联组织的外键（对应Organization.OrgPK）
  • PersonFK：人员唯一标识
  • isPrimary：是否为人员的主要组织（1=是）

方案一：直接多次自连接查询 #

通过多次自连Organization表，直接获取当前组织、父级、祖父级的名称，逻辑直观：

SELECT
    po.PersonFK,
    o.OrgName AS 当前组织名称,
    p.OrgName AS 父级组织名称,
    gp.OrgName AS 祖父级组织名称
FROM
    personOrganization po
-- 关联人员的主要组织
INNER JOIN Organization o 
    ON po.OrgFK = o.OrgPK
-- 关联父级组织（用LEFT JOIN避免过滤无父级的组织）
LEFT JOIN Organization p 
    ON o.ParentOrgPK = p.OrgPK
-- 关联祖父级组织（同样用LEFT JOIN兼容无祖父级的场景）
LEFT JOIN Organization gp 
    ON p.ParentOrgPK = gp.OrgPK
WHERE
    po.isPrimary = 1;

方案二：先构建派生表再关联 #

先通过CTE（公共表表达式）生成包含组织及父级信息的派生表，再关联祖父组织，适合后续需要复用层级逻辑的场景：

WITH OrgParentHierarchy AS (
    SELECT
        o.OrgPK,
        o.OrgName AS 当前组织名称,
        p.OrgName AS 父级组织名称,
        p.OrgPK AS 父级组织PK -- 用于后续关联祖父级
    FROM
        Organization o
    LEFT JOIN Organization p 
        ON o.ParentOrgPK = p.OrgPK
)
SELECT
    po.PersonFK,
    oph.当前组织名称,
    oph.父级组织名称,
    gp.OrgName AS 祖父级组织名称
FROM
    personOrganization po
INNER JOIN OrgParentHierarchy oph 
    ON po.OrgFK = oph.OrgPK
LEFT JOIN Organization gp 
    ON oph.父级组织PK = gp.OrgPK
WHERE
    po.isPrimary = 1;

注意事项 #

• 使用LEFT JOIN而非INNER JOIN是为了保留那些没有父级/祖父级的组织记录，此时对应的层级名称会显示为NULL；如果业务要求只返回有完整三级层级的记录，可替换为INNER JOIN。
• 由于主键/外键非连续数值，完全依赖表间的关联字段（ParentOrgPK关联OrgPK）实现层级查询，无需数值运算。

内容的提问来源于stack exchange，提问作者KenM