SQL查询非空值返回异常：取错最近有效FeedbackID问题排查

针对OccupancyID 182的查询结果符合预期，但OccupancyID 3313的2024-03-01记录，返回的PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID为150015，而非预期的100812。需求是让每个日期区间自动沿用最近一次的非空反馈ID，3313的100812从2024-01-01生效后，应一直沿用至下一次变更。

预期结果 #

原查询代码 #

drop table if exists #temp2

CREATE TABLE #temp2
(
    [FirstDayOfMonth] [date] NOT NULL,
    [OccupancyID] [int] NULL,
    [CustomerVoice_RepairsSatisfactionFeedbackID] [int] NULL,
    [RowNumber] [bigint] NULL
)
GO
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 3313, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 3313, 150015, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 3313, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 3313, 100812, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 3313, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 3313, NULL, 6)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 182, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 182, 100571, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 182, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 182, NULL, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 182, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 182, NULL, 6)
GO

;WITH CTE AS 
(
    SELECT
        FirstDayOfMonth, 
        OccupancyID, 
        CustomerVoice_RepairsSatisfactionFeedbackID,
        RowNumber,
        LAG(CustomerVoice_RepairsSatisfactionFeedbackID) OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS Lag_Value,
        ROW_NUMBER() OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS RN,
        CASE WHEN CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL THEN 1 ELSE 0 END AS Flag            
    FROM 
        #temp2
)
SELECT 
    OccupancyID, 
    FirstDayOfMonth, 
    CustomerVoice_RepairsSatisfactionFeedbackID,
    CASE 
        WHEN Lag_Value IS NULL THEN PreviousCustomerVoice_RepairsSatisfactionFeedbackID
        ELSE Lag_Value 
    END AS PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
FROM 
(
    SELECT *,
    (
        SELECT 
            TOP(1) CustomerVoice_RepairsSatisfactionFeedbackID 
        FROM 
            CTE c
        WHERE 
            c.Flag = 1 AND 
            c.RN < a.RN AND 
            c.OccupancyID = a.OccupancyID
    ) AS PreviousCustomerVoice_RepairsSatisfactionFeedbackID
    FROM CTE 
a) a

问题原因分析 #

1. 嵌套子查询逻辑缺陷：原查询中SELECT TOP(1)...的子查询未按RN DESC排序，导致返回的是最早出现的非空ID，而非最近的。比如3313的2024-03-01行，子查询会返回RN=2的150015，而不是RN=4的100812。
2. 外层CASE判断错误：依赖Lag_Value返回结果，但Lag_Value仅取上一行的值，当连续多行是NULL时，Lag_Value也会是NULL，无法延续最新的非空ID；同时当当前行有新ID时，Lag_Value是上一个ID，后续行需要沿用新ID，而非依赖上一行的值。

修复方案 #

方案1：SQL Server 2022+ 版本（简洁高效） #

利用LAST_VALUE结合IGNORE NULLS直接获取截至当前行的最近非空ID：

drop table if exists #temp2

CREATE TABLE #temp2
(
    [FirstDayOfMonth] [date] NOT NULL,
    [OccupancyID] [int] NULL,
    [CustomerVoice_RepairsSatisfactionFeedbackID] [int] NULL,
    [RowNumber] [bigint] NULL
)
GO
-- 插入数据与原查询一致
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 3313, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 3313, 150015, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 3313, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 3313, 100812, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 3313, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 3313, NULL, 6)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 182, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 182, 100571, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 182, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 182, NULL, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 182, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 182, NULL, 6)
GO

;WITH CTE AS (
    SELECT
        FirstDayOfMonth,
        OccupancyID,
        CustomerVoice_RepairsSatisfactionFeedbackID,
        -- 获取截至当前行的最近非空ID，忽略NULL值
        LAST_VALUE(CustomerVoice_RepairsSatisfactionFeedbackID) OVER(
            PARTITION BY OccupancyID 
            ORDER BY FirstDayOfMonth 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS LatestNonNULLFeedbackID
    FROM #temp2
)
SELECT
    OccupancyID,
    FirstDayOfMonth,
    CustomerVoice_RepairsSatisfactionFeedbackID,
    -- 逻辑处理：当前行有新ID时，Previous取上一个最新ID；否则取当前最新ID
    CASE
        WHEN CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL THEN
            LAG(LatestNonNULLFeedbackID) OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth)
        ELSE LatestNonNULLFeedbackID
    END AS PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
FROM CTE
ORDER BY OccupancyID, FirstDayOfMonth;

方案2：兼容低版本SQL Server #

通过累积分组的方式，将连续NULL与最近的非空ID归为一组，再取每组的非空ID：

drop table if exists #temp2

CREATE TABLE #temp2
(
    [FirstDayOfMonth] [date] NOT NULL,
    [OccupancyID] [int] NULL,
    [CustomerVoice_RepairsSatisfactionFeedbackID] [int] NULL,
    [RowNumber] [bigint] NULL
)
GO
-- 插入数据与原查询一致
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 3313, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 3313, 150015, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 3313, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 3313, 100812, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 3313, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 3313, NULL, 6)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 182, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 182, 100571, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 182, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 182, NULL, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 182, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 182, NULL, 6)
GO

;WITH CTE AS (
    SELECT
        *,
        -- 累积非空ID出现的次数，形成分组ID
        SUM(CASE WHEN CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL THEN 1 ELSE 0 END) OVER(
            PARTITION BY OccupancyID ORDER BY FirstDayOfMonth
        ) AS GroupID
    FROM #temp2
),
GroupedCTE AS (
    -- 每个分组取唯一的非空ID
    SELECT
        OccupancyID,
        GroupID,
        MAX(CustomerVoice_RepairsSatisfactionFeedbackID) AS GroupFeedbackID
    FROM CTE
    GROUP BY OccupancyID, GroupID
)
SELECT
    c.OccupancyID,
    c.FirstDayOfMonth,
    c.C