SQL Server 2008递归CTE查询上级配置值实现方案问询

没问题，我来给你写一个完全符合需求的实现方案，包括测试数据、单个节点查询和批量查询的示例，逻辑清晰且贴合SQL Server 2008的语法。

先准备测试数据 #

首先我们创建两张测试表并插入符合你描述的层级和配置数据：

-- 创建客户层级表
CREATE TABLE customertest (
    cid INT PRIMARY KEY,
    upid INT NULL REFERENCES customertest(cid)
);

-- 创建配置表
CREATE TABLE conftest (
    cid INT REFERENCES customertest(cid),
    confname VARCHAR(50),
    confvalue VARCHAR(50),
    PRIMARY KEY (cid, confname)
);

-- 插入层级数据（对应你说的示例结构）
INSERT INTO customertest VALUES
(1, NULL),   -- 顶级节点
(2, 1),
(3, 2),
(4, 3),
(5, 1),
(6, 5),
(7, 6),
(8, 7);

-- 插入配置数据（只有1和6节点有test_conf配置）
INSERT INTO conftest VALUES
(1, 'test_conf', '100'),
(6, 'test_conf', '200');

单个目标节点的查询实现 #

这个CTE会从目标节点开始向上递归，直到找到第一个包含指定配置的上级节点：

DECLARE @TargetCID INT = 4; -- 可替换为7、8等节点测试
DECLARE @TargetConfName VARCHAR(50) = 'test_conf';

WITH RecursiveConfig AS (
    -- 锚点成员：从目标节点开始查询
    SELECT 
        ct.cid,
        ct.upid,
        cf.confvalue,
        1 AS RecursionLevel  -- 记录递归层级，方便找到最近的上级配置
    FROM customertest ct
    LEFT JOIN conftest cf 
        ON ct.cid = cf.cid AND cf.confname = @TargetConfName
    WHERE ct.cid = @TargetCID

    UNION ALL

    -- 递归成员：仅当当前节点无配置时，向上查询上级节点
    SELECT 
        parent.cid,
        parent.upid,
        parent_cf.confvalue,
        rc.RecursionLevel + 1 AS RecursionLevel
    FROM RecursiveConfig rc
    JOIN customertest parent 
        ON rc.upid = parent.cid
    LEFT JOIN conftest parent_cf 
        ON parent.cid = parent_cf.cid AND parent_cf.confname = @TargetConfName
    WHERE rc.confvalue IS NULL -- 无配置才继续递归
)
-- 取层级最小的有效配置（也就是离目标节点最近的上级配置）
SELECT TOP 1 confvalue AS InheritedConfigValue
FROM RecursiveConfig
WHERE confvalue IS NOT NULL
ORDER BY RecursionLevel ASC;

测试结果：

• 当@TargetCID=4时，返回100
• 当@TargetCID=7或8时，返回200

批量查询所有节点的继承配置 #

如果需要一次性获取所有节点的继承配置，可以用这个版本：

DECLARE @TargetConfName VARCHAR(50) = 'test_conf';

WITH RecursiveConfig AS (
    -- 锚点成员：所有节点作为起始点
    SELECT 
        ct.cid AS OriginalCID, -- 保留原始节点ID
        ct.cid,
        ct.upid,
        cf.confvalue,
        1 AS RecursionLevel
    FROM customertest ct
    LEFT JOIN conftest cf 
        ON ct.cid = cf.cid AND cf.confname = @TargetConfName

    UNION ALL

    -- 递归成员：向上找上级，直到找到配置
    SELECT 
        rc.OriginalCID, -- 始终保留原始节点ID
        parent.cid,
        parent.upid,
        parent_cf.confvalue,
        rc.RecursionLevel + 1 AS RecursionLevel
    FROM RecursiveConfig rc
    JOIN customertest parent 
        ON rc.upid = parent.cid
    LEFT JOIN conftest parent_cf 
        ON parent.cid = parent_cf.cid AND parent_cf.confname = @TargetConfName
    WHERE rc.confvalue IS NULL
)
-- 每个原始节点取第一个找到的配置
SELECT 
    DISTINCT OriginalCID AS cid,
    FIRST_VALUE(confvalue) OVER (PARTITION BY OriginalCID ORDER BY RecursionLevel ASC) AS InheritedConfigValue
FROM RecursiveConfig
WHERE confvalue IS NOT NULL
ORDER BY OriginalCID;

这个查询会返回所有节点的继承配置结果，比如cid=4返回100，cid=7、8返回200，cid=1返回100，cid=6返回200等。

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