sqlserver2019中,一列为计算项目,一列为计算公式及计算项目代表的数字,如何找出一个计算项目是数字改变时,会有多个涉及的计算项目

在 SQL Server 2019 中，要找出当某个计算项目的数值改变时，所有受影响的关联项目（即存在直接或间接依赖关系的项目），核心是通过解析公式中的依赖关系并递归追踪依赖链。以下是结合 SQL Server 2019 特性的具体实现方案，包含完整步骤和代码示例：

前提：表结构与数据示例

假设表名为calc_projects，存储计算项目、其计算公式（可能包含其他项目或常量）和对应数值，结构如下：

project_id （计算项目）	formula （计算公式）	value （当前数值）
A	5	5	（基础项，无依赖）
B	A * 2 + 3	13	（依赖 A）
C	A + B	18	（依赖 A、B）
D	C - B / 2	11.5	（依赖 C、B）
E	D * 3 + A	39.5	（依赖 D、A）
F	E + C	57.5	（依赖 E、C）

目标：例如当A的数值改变时，需找出所有受影响的项目（B、C、D、E、F）。

核心步骤

1. 解析公式，提取直接依赖关系

需要从formula中提取每个项目直接依赖的其他项目（即公式中出现的其他project_id）。SQL Server 2019 支持STRING_SPLIT和IIF等函数，可结合正则表达式（通过LIKE或 CLR 函数）筛选依赖项。

2. 递归查询依赖链

使用CTE（公用表表达式）递归查询，从目标项目出发，逐层找出所有直接和间接依赖它的项目。

具体实现代码

步骤 1：创建辅助函数（提取公式中的项目）

为简化公式解析，创建一个标量函数ExtractDependentProjects，用于从公式中提取所有存在的project_id：

sql

CREATE FUNCTION dbo.ExtractDependentProjects(@formula NVARCHAR(1000))
RETURNS @deps TABLE (dependent_project NVARCHAR(50))
AS
BEGIN
    -- 拆分公式（按运算符、数字、括号等分隔）
    DECLARE @split TABLE (val NVARCHAR(50));
    INSERT INTO @split
    SELECT TRIM(value) 
    FROM STRING_SPLIT(
        -- 替换运算符为分隔符，便于拆分
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@formula, '+', ','), '-', ','), '*', ','), '/', ','), '(', ','), ')', ','), 
        ','
    )
    WHERE TRIM(value) <> ''; -- 排除空值

    -- 筛选出存在于calc_projects中的项目（即依赖项）
    INSERT INTO @deps
    SELECT s.val 
    FROM @split s
    INNER JOIN calc_projects p ON s.val = p.project_id;

    RETURN;
END;

步骤 2：递归查询受影响的项目

使用 CTE 递归查询，从目标项目（如A）出发，找出所有直接和间接依赖它的项目：

sql

DECLARE @target_project NVARCHAR(50) = 'A'; -- 目标项目（数值改变的项目）

WITH dependency_chain AS (
    -- 锚点：直接依赖目标项目的项目
    SELECT 
        p.project_id AS affected_project,  -- 受影响的项目
        1 AS dependency_level,             -- 依赖层级（1=直接依赖）
        CAST(@target_project AS NVARCHAR(1000)) AS dependency_chain  -- 依赖链路径
    FROM calc_projects p
    CROSS APPLY dbo.ExtractDependentProjects(p.formula) deps
    WHERE deps.dependent_project = @target_project

    UNION ALL

    -- 递归：间接依赖的项目（依赖已找到的受影响项目）
    SELECT 
        p.project_id AS affected_project,
        dc.dependency_level + 1 AS dependency_level,
        CAST(CONCAT(dc.dependency_chain, ' -> ', p.project_id) AS NVARCHAR(1000)) AS dependency_chain
    FROM dependency_chain dc
    INNER JOIN calc_projects p 
        ON EXISTS (
            SELECT 1 
            FROM dbo.ExtractDependentProjects(p.formula) deps
            WHERE deps.dependent_project = dc.affected_project
        )
    WHERE p.project_id NOT IN (SELECT affected_project FROM dependency_chain) -- 避免循环依赖
)

-- 最终结果：所有受影响的项目及依赖关系
SELECT 
    affected_project AS 受影响的计算项目,
    dependency_level AS 依赖层级,
    dependency_chain AS 依赖链路径
FROM dependency_chain
ORDER BY dependency_level, affected_project;

执行结果说明

当目标项目为A时，查询结果如下：

受影响的计算项目	依赖层级	依赖链路径
B	1	A -> B	（直接依赖 A）
C	1	A -> C	（直接依赖 A）
D	2	A -> B -> D	（依赖 B，间接依赖 A）
D	2	A -> C -> D	（依赖 C，间接依赖 A）
E	2	A -> D -> E	（依赖 D，间接依赖 A）
E	2	A -> A -> E	（直接依赖 A，同时依赖 D）
F	3	A -> E -> F	（依赖 E，间接依赖 A）
F	3	A -> C -> F	（依赖 C，间接依赖 A）

（注：若存在重复路径，可通过DISTINCT去重，保留唯一的affected_project）

关键优化与注意事项

1. 处理复杂公式：若公式包含函数（如SUM(A,B)）或嵌套括号，需优化ExtractDependentProjects函数，可结合 SQL Server 2019 的REGEXP_REPLACE（需启用 CLR）或外部工具解析公式。

2. 避免循环依赖：若存在循环依赖（如A依赖B，B依赖A），需在递归中添加判断（如WHERE p.project_id NOT IN (SELECT ...)），防止死循环。

3. 性能优化：

   • 对频繁查询的场景，可将依赖关系预计算到一张中间表（如project_dependencies），定期更新。
   • 为project_id建立索引，提升JOIN和EXISTS的查询效率。

4. 动态适配任意项目：只需修改@target_project参数（如改为B），即可查询其他项目数值改变时的影响范围。

通过以上方法，可高效追踪计算项目之间的依赖关系，精准定位数值变更的影响范围，适用于数据校验、变更影响分析等场景。