SQL Server中如何获取指定ordernr基于start_date的上一条article？

表结构 #

mixes表的相关列及数据如下：

需求 #

基于start_date获取指定ordernr对应的上一条article。

尝试过程及问题 #

执行以下SQL时，能得到正确的上一条article和ordernr：

SELECT TOP (1) 
    article, ordernr,
    LAG(ordernr) OVER (ORDER BY start_date) AS prev_ordernr,
    LAG(article) OVER (ORDER BY start_date) AS prev_article
FROM 
    mixes
ORDER BY 
    start_date DESC

但添加WHERE ordernr = '10004'筛选特定行时，prev_article和prev_ordernr返回NULL，需要解决该问题。

问题根源 #

LAG函数是在当前查询的结果集范围内计算前一行数据的。当你直接添加WHERE ordernr = '10004'时，查询结果集仅包含这一行数据，没有前序行，因此返回NULL。必须先计算所有行的LAG值，再筛选目标行。

正确SQL写法 #

方法1：使用CTE（公共表表达式）

WITH ranked_mixes AS (
    SELECT 
        article, ordernr,
        LAG(ordernr) OVER (ORDER BY start_date) AS prev_ordernr,
        LAG(article) OVER (ORDER BY start_date) AS prev_article
    FROM mixes
    WHERE start_date IS NOT NULL -- 可选：排除无start_date的行，按需调整
)
SELECT article, ordernr, prev_ordernr, prev_article
FROM ranked_mixes
WHERE ordernr = '10004';

方法2：使用子查询

SELECT article, ordernr, prev_ordernr, prev_article
FROM (
    SELECT 
        article, ordernr,
        LAG(ordernr) OVER (ORDER BY start_date) AS prev_ordernr,
        LAG(article) OVER (ORDER BY start_date) AS prev_article
    FROM mixes
    WHERE start_date IS NOT NULL -- 可选：排除无start_date的行
) AS sub_query
WHERE ordernr = '10004';

补充说明 #

• 若需要包含start_date为空的行，去掉WHERE start_date IS NOT NULL即可，但空值在排序时的位置取决于数据库（SQL Server默认空值排在最前）。
• 针对ordernr=10004的行，按start_date排序后，前一行是ordernr=10001（start_date=2024-01-01），最终返回的prev_article为a，prev_ordernr为10001。

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