SQL Server中如何通过WHERE条件筛选LAG函数计算出的负差值结果

这个问题我之前也碰到过！核心原因是SQL的执行顺序：窗口函数是在WHERE子句之后才执行的，所以你直接在WHERE里用窗口函数的表达式或者别名都不会生效；而HAVING子句是用来过滤GROUP BY聚合后的结果，这里也不适用。

要实现直接筛选DIFF小于0的记录，你需要先把窗口函数计算出的DIFF字段生成出来，再对这个中间结果集进行筛选，常用的两种方式是子查询和CTE（公共表表达式）：

方式一：使用子查询 #

SELECT *
FROM (
    SELECT 
        NUMBER,
        DATE_TIME,
        NUMBER - LAG(NUMBER) OVER (ORDER BY DATE_TIME) AS DIFF
    FROM exampleTable WITH(NOLOCK)
    WHERE CONDITION1='abcdef' AND DATE_TIME >='20220801'
) AS temp_result
WHERE DIFF < 0
ORDER BY DATE_TIME ASC;

方式二：使用CTE（可读性更好） #

WITH temp_result AS (
    SELECT 
        NUMBER,
        DATE_TIME,
        NUMBER - LAG(NUMBER) OVER (ORDER BY DATE_TIME) AS DIFF
    FROM exampleTable WITH(NOLOCK)
    WHERE CONDITION1='abcdef' AND DATE_TIME >='20220801'
)
SELECT *
FROM temp_result
WHERE DIFF < 0
ORDER BY DATE_TIME ASC;

补充说明： #

• 两种方式的本质都是先完成窗口函数的计算，生成包含DIFF的中间结果，再在外层查询中用WHERE筛选DIFF<0的记录。
• 结果集中的第一条记录因为没有前序值，DIFF会是NULL，而NULL < 0的判断结果是未知，所以这条记录会被自动排除，不需要额外处理。

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