基于T-SQL计算每次检查后未解决违规项的技术求助

问题背景 #

检查记录表规则如下：

• 首次检查（Inspection 1）记录所有违规项（可超过15种）
• 后续每次检查仅记录已解决的违规项，已解决项不会再出现在未解决列表中
• 部分检查可能无已解决项（需保留该检查的记录）

示例数据表：

需求 #

获取每次检查完成后，当前未解决的违规项列表。

现有尝试的问题 #

熟悉LAG、LEAD、FIRST_VALUE等窗口函数，尝试通过自定义函数计算违规项差值，但无法处理第二次检查之后的逻辑（比如检查3之后，应基于检查2的剩余项计算，而非始终用首次检查的列表），现寻求正确的T-SQL解决方案。可调整底层数据查询逻辑，仅需保留每次检查的已解决违规项记录。

现有代码 #

主查询代码 #

SELECT aggdata.Inspection
     , RemainingViolations =
                            CASE
                                WHEN aggdata.Inspection = 1 THEN aggdata.AggregatedViolations
                                ELSE dbo.udf_GetDifferenceInValues(FIRST_VALUE(aggdata.AggregatedViolations) OVER (ORDER BY aggdata.Inspection), aggdata.AggregatedViolations)
                            END
FROM (
    SELECT SimpleData.Inspection
         , AggregatedViolations = STRING_AGG(SimpleData.Violation, ',')
    FROM (VALUES (1, '6/1/2024', 100),
    (1, '6/1/2024', 101),
    (1, '6/1/2024', 102),
    (1, '6/1/2024', 103),
    (2, '6/1/2024', 100),
    (2, '6/1/2024', 101),
    (3, '6/1/2024', NULL),
    (4, '6/1/2024', 103),
    (5, '6/1/2024', 102)
    ) AS
    SimpleData (Inspection, DateInspection, Violation)
    GROUP BY SimpleData.Inspection
) aggdata

自定义函数代码 #

ALTER FUNCTION udf_GetDifferenceInValues
(
      -- 函数参数
      @String1 VARCHAR(4000)
    , @String2 VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN

    -- 声明返回变量
    DECLARE @Result VARCHAR(4000)

    -- 计算差值：从@String1中排除@String2包含的项
    SELECT @Result = STRING_AGG(Value, ',')
    FROM dbo.dbSplit(@String1, ',')
    WHERE Value NOT IN (
            SELECT Value
            FROM dbo.dbSplit(@String2, ',')
        )

    -- 返回结果
    RETURN @Result
END

解决方案 #

核心思路是逐次迭代维护未解决违规项集合，而非始终基于首次检查的列表计算。可使用递归CTE实现：

WITH InspectionData AS (
    -- 预处理：过滤NULL违规项，整理每次检查的已解决项
    SELECT 
        Inspection,
        Date,
        Violation
    FROM YourTableName
    WHERE Violation IS NOT NULL
),
RecursiveRemaining AS (
    -- 递归基准：首次检查的所有违规项为初始未解决列表
    SELECT 
        Inspection,
        Date,
        CAST(STRING_AGG(Violation, ',') AS VARCHAR(4000)) AS RemainingViolations
    FROM InspectionData
    WHERE Inspection = 1
    GROUP BY Inspection, Date

    UNION ALL

    -- 递归步骤：基于上一次的未解决列表，排除当前检查的已解决项
    SELECT 
        curr.Inspection,
        curr.Date,
        STRING_AGG(v.Value, ',') AS RemainingViolations
    FROM RecursiveRemaining prev
    JOIN (
        -- 合并有已解决项和无已解决项的检查记录
        SELECT DISTINCT Inspection, Date FROM InspectionData
        UNION ALL
        SELECT Inspection, Date FROM YourTableName WHERE Violation IS NULL
    ) curr ON curr.Inspection = prev.Inspection + 1
    -- 拆分上一次的剩余违规项为单个值
    CROSS APPLY dbo.dbSplit(prev.RemainingViolations, ',') v
    -- 排除本次已解决的项
    WHERE v.Value NOT IN (
        SELECT Violation FROM InspectionData WHERE Inspection = curr.Inspection
    )
    GROUP BY curr.Inspection, curr.Date
)
-- 输出所有检查记录，无已解决项的检查沿用上次的剩余列表
SELECT 
    t.Inspection,
    t.Date,
    ISNULL(r.RemainingViolations, 
           (SELECT RemainingViolations FROM RecursiveRemaining WHERE Inspection = t.Inspection - 1)
          ) AS RemainingViolations
FROM YourTableName t
LEFT JOIN RecursiveRemaining r ON t.Inspection = r.Inspection
GROUP BY t.Inspection, t.Date, r.RemainingViolations
ORDER BY t.Inspection;

方案说明 #

1. InspectionData CTE：过滤无效的NULL违规项，仅保留有效已解决记录。
2. RecursiveRemaining CTE：
   • 基准部分：获取首次检查的所有违规项作为初始未解决列表。
   • 递归部分：每次检查基于上一次的未解决列表，移除本次已解决项，得到当前剩余违规项。
3. 最终查询：关联原表所有检查记录，无已解决项的检查直接沿用上次的剩余违规项列表。

注意事项 #

• 确保dbo.dbSplit函数能正确拆分字符串为单个值。
• 若违规项数量较多，建议改用表值函数或JSON存储集合，避免字符串拼接的长度限制。

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