基于日期获取ID的历史记录问题（含同日期多记录场景）

你的核心需求是：每个ID下，当前日期的每条记录需要关联上一个更早日期的所有记录；如果是该ID的最早日期记录，则上一条记录为NULL。原LAG函数无法满足，因为它是按行偏移，同日期的行之间会互相引用，而非关联上一个日期的全部记录。

方法：通过日期分组+左连接实现一对多关联 #

以下SQL可以实现你的预期结果：

WITH DateGroups AS (
    -- 提取每个ID的唯一日期，并获取每个日期对应的上一个日期
    SELECT 
        ID,
        Date,
        LAG(Date) OVER (PARTITION BY ID ORDER BY Date) AS PreviousDate
    FROM (
        SELECT DISTINCT ID, Date 
        FROM [Source Table]
    ) AS DistinctDates
),
PreviousRecords AS (
    -- 获取每个当前日期对应的所有上一个日期的记录
    SELECT 
        dg.ID,
        dg.Date AS CurrentDate,
        dg.PreviousDate,
        st.Rec AS PreviousRec
    FROM DateGroups dg
    LEFT JOIN [Source Table] st 
        ON dg.ID = st.ID 
        AND dg.PreviousDate = st.Date
)
-- 将原表与上一个日期的记录做左连接，得到最终结果
SELECT 
    st.ID,
    st.Rec AS [记录编号（Rec）],
    st.Date AS [日期（Date）],
    pr.PreviousRec AS [上一条记录（Previous Rec）],
    pr.PreviousDate AS [上一条日期（Previous Date）]
FROM [Source Table] st
LEFT JOIN PreviousRecords pr 
    ON st.ID = pr.ID 
    AND st.Date = pr.CurrentDate
ORDER BY st.ID, st.Date, st.Rec;

逻辑说明 #

1. DateGroups：先提取每个ID的唯一日期集合，再用LAG窗口函数为每个日期匹配上一个更早的日期，避免同日期重复计算。
2. PreviousRecords：将日期分组结果与源表连接，获取每个当前日期对应的所有上一个日期的记录（形成一对多的映射）。
3. 最终查询：将原表与PreviousRecords左连接，让原表中每条当前日期的记录都关联上一个日期的所有记录，得到预期的结果。

替代方案：用日期序号关联 #

如果你的SQL环境对窗口函数支持有限，可以用行号来关联上一个日期：

WITH RankedDates AS (
    -- 为每个ID的日期按顺序编号
    SELECT 
        ID,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS DateRank
    FROM (
        SELECT DISTINCT ID, Date 
        FROM [Source Table]
    ) AS DistinctDates
),
CurrentAndPreviousDates AS (
    -- 匹配当前日期与上一个日期
    SELECT 
        rd.ID,
        rd.Date AS CurrentDate,
        rd_prev.Date AS PreviousDate
    FROM RankedDates rd
    LEFT JOIN RankedDates rd_prev 
        ON rd.ID = rd_prev.ID 
        AND rd.DateRank = rd_prev.DateRank + 1
),
PreviousRecords AS (
    SELECT 
        capd.ID,
        capd.CurrentDate,
        capd.PreviousDate,
        st.Rec AS PreviousRec
    FROM CurrentAndPreviousDates capd
    LEFT JOIN [Source Table] st 
        ON capd.ID = st.ID 
        AND capd.PreviousDate = st.Date
)
SELECT 
    st.ID,
    st.Rec AS [记录编号（Rec）],
    st.Date AS [日期（Date）],
    pr.PreviousRec AS [上一条记录（Previous Rec）],
    pr.PreviousDate AS [上一条日期（Previous Date）]
FROM [Source Table] st
LEFT JOIN PreviousRecords pr 
    ON st.ID = pr.ID 
    AND st.Date = pr.CurrentDate
ORDER BY st.ID, st.Date, st.Rec;

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