如何排除SQL查询中计算列PreviousCurrent的NULL值

问题原因 #

HAVING 子句仅适用于聚合分组后的结果过滤，而你这里的PreviousCurrent是窗口函数LAG生成的计算列——窗口函数的执行时机在WHERE之后、ORDER BY之前，所以直接用WHERE或HAVING都无法直接过滤这个列的NULL值。

解决方案 #

把原查询包装成子查询或CTE（公共表表达式），在外层查询中添加过滤条件PreviousCurrent IS NOT NULL即可。

方式1：使用CTE（可读性更优）

WITH OriginalQuery AS (
    SELECT 
        [WAAPID] AS Bus_Number,
        FWCPLM *-.01 AS Completed_Miles,
        (FWCPLM *.01) - LAG((FWCPLM *.01)) OVER (PARTITION BY LTRIM(RTRIM([WAAPID])) ORDER BY FWCPLM, PM_Completed_date.standarddate) AS PreviousCurrent,
        CONVERT(DATE, PM_Completed_Date.StandardDate) AS Completed_Date,
        [WAMCU],
        [WAWR01],
        CASE 
            WHEN WAWR01 = '4004' THEN 'A INSP' 
            WHEN WAWR01 = '4003' THEN 'B INSP' 
            WHEN WAWR01 = '4002' THEN 'C INSP' 
            WHEN WAWR01 = '4001' THEN 'D INSP' 
            ELSE 'Active' 
        END AS [PM TYPE]
    FROM  
        [JDE_PRODUCTION].[PRODDTA].[F4801] WO
    INNER JOIN 
        PRODDTA.F1207 PM ON PM.FWWONA = WO.WADOCO
    CROSS APPLY
        dbo.tvf_JulianDateToStandardDate(PM.FWCPLD) PM_Completed_Date
    CROSS APPLY
        dbo.tvf_JulianDateToStandardDate(PM.FWLCPD) PM_Last_CompletedDate
    WHERE 
        WAWR01 IN ('4004')
        AND WATYPS = '6'
        AND PM_Completed_Date.StandardDate >= '2019-01-01' -- 建议使用标准日期格式YYYY-MM-DD
        AND WASRST <> '98'
)
SELECT *
FROM OriginalQuery
WHERE PreviousCurrent IS NOT NULL
ORDER BY Bus_Number, Completed_Miles ASC, WAMCU;

方式2：使用子查询

SELECT *
FROM (
    SELECT 
        [WAAPID] AS Bus_Number,
        FWCPLM *-.01 AS Completed_Miles,
        (FWCPLM *.01) - LAG((FWCPLM *.01)) OVER (PARTITION BY LTRIM(RTRIM([WAAPID])) ORDER BY FWCPLM, PM_Completed_date.standarddate) AS PreviousCurrent,
        CONVERT(DATE, PM_Completed_Date.StandardDate) AS Completed_Date,
        [WAMCU],
        [WAWR01],
        CASE 
            WHEN WAWR01 = '4004' THEN 'A INSP' 
            WHEN WAWR01 = '4003' THEN 'B INSP' 
            WHEN WAWR01 = '4002' THEN 'C INSP' 
            WHEN WAWR01 = '4001' THEN 'D INSP' 
            ELSE 'Active' 
        END AS [PM TYPE]
    FROM  
        [JDE_PRODUCTION].[PRODDTA].[F4801] WO
    INNER JOIN 
        PRODDTA.F1207 PM ON PM.FWWONA = WO.WADOCO
    CROSS APPLY
        dbo.tvf_JulianDateToStandardDate(PM.FWCPLD) PM_Completed_Date
    CROSS APPLY
        dbo.tvf_JulianDateToStandardDate(PM.FWLCPD) PM_Last_CompletedDate
    WHERE 
        WAWR01 IN ('4004')
        AND WATYPS = '6'
        AND PM_Completed_Date.StandardDate >= '2019-01-01'
        AND WASRST <> '98'
) AS SubQuery
WHERE PreviousCurrent IS NOT NULL
ORDER BY Bus_Number, Completed_Miles ASC, WAMCU;

额外提示 #

• 建议使用**标准日期格式YYYY-MM-DD**替代'01/01/2019'，避免因数据库日期格式设置导致的解析错误。
• 列名尽量避免用单引号包裹，改用方括号[]（SQL Server语法），更符合语法规范。

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