基于MS SQL Server的树形分支记录校验及示例实现

这里给你一个精准匹配需求的SQL实现，利用MS SQL Server的递归CTE来处理树形结构的分支遍历，最终输出你需要的人员归属结果：

WITH AdultsBranch AS (
    -- 锚点成员：定位Adults分支的根节点
    SELECT profile_id
    FROM core_profile
    WHERE profile_name = 'Adult'
    UNION ALL
    -- 递归遍历该分支下所有子节点
    SELECT cp.profile_id
    FROM core_profile cp
    INNER JOIN AdultsBranch ab ON cp.parent_profile_id = ab.profile_id
),
ChildrenBranch AS (
    -- 锚点成员：定位Children分支的根节点
    SELECT profile_id
    FROM core_profile
    WHERE profile_name = 'Children'
    UNION ALL
    -- 递归遍历该分支下所有子节点
    SELECT cp.profile_id
    FROM core_profile cp
    INNER JOIN ChildrenBranch cb ON cp.parent_profile_id = cb.profile_id
)
SELECT 
    p.person_id,
    p.first_name,
    p.last_name,
    -- 判断当前人员是否属于Adults分支
    IIF(EXISTS(SELECT 1 FROM core_profile_member pm WHERE pm.person_id = p.person_id AND pm.profile_id IN (SELECT profile_id FROM AdultsBranch)), 'T', 'F') AS Adults,
    -- 判断当前人员是否属于Childrens分支
    IIF(EXISTS(SELECT 1 FROM core_profile_member pm WHERE pm.person_id = p.person_id AND pm.profile_id IN (SELECT profile_id FROM ChildrenBranch)), 'T', 'F') AS Childrens
FROM core_person p
ORDER BY p.person_id;

代码逻辑说明 #

1. 递归CTE遍历树形分支：
   • AdultsBranch 和 ChildrenBranch 分别通过递归逻辑，把对应分支的根节点（比如Adult、Children）以及所有层级的子节点全部收集起来，确保不会遗漏分支下的任何子配置
2. 人员归属判断：
   • 用IIF结合EXISTS子查询，检查当前人员的关联记录是否出现在目标分支的配置节点中，存在则标记'T'，否则标记'F'
3. 输出匹配示例：
   运行这段SQL后，会完全输出你期望的结果：

   person_id	first_name	last_name	Adults	Childrens
   1	Joe	Doe	T	T
   2	Jill	Doe	F	T

测试数据准备（可选） #

如果需要本地验证，可以先创建测试表并插入示例数据：

-- 创建人员表
CREATE TABLE core_person (
    person_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);
INSERT INTO core_person VALUES (1, 'Joe', 'Doe'), (2, 'Jill', 'Doe');

-- 创建树形配置表
CREATE TABLE core_profile (
    profile_id INT PRIMARY KEY,
    parent_profile_id INT,
    profile_name VARCHAR(50)
);
INSERT INTO core_profile VALUES 
(1, NULL, 'Main Campus'),
(2, 1, 'Adult'),
(3, 2, 'Usher'),
(4, 1, 'Children'),
(5, 4, 'Teacher');

-- 创建配置-人员关联表
CREATE TABLE core_profile_member (
    profile_id INT,
    person_id INT,
    PRIMARY KEY (profile_id, person_id)
);
INSERT INTO core_profile_member VALUES (3,1), (5,2), (5,1);

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