SQL查询：筛选DEPARTURE与ARRIVAL交替的序列数据

要解决这个问题，核心是按主体（如人员ID）分组，按时间排序后验证每一条记录的方向是否符合严格交替规则——必须以DEPARTURE开头，之后依次是ARRIVAL、DEPARTURE、ARRIVAL...，同时过滤掉连续相同方向的记录。

假设你的表包含唯一标识主体的字段（比如PersonID）和时间字段（比如Crossing_Time），以下是可行的SQL方案：

WITH OrderedCrossings AS (
    SELECT 
        *,
        -- 按主体分组，按时间排序生成序号
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Crossing_Time) AS RowNum,
        -- 标记当前记录的方向是否符合预期（DEPARTURE对应奇数行，ARRIVAL对应偶数行）
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Crossing_Time) % 2 = 1 AND Crossing_Direction = 'DEPARTURE' THEN 1
            WHEN ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Crossing_Time) % 2 = 0 AND Crossing_Direction = 'ARRIVAL' THEN 1
            ELSE 0
        END AS IsValid
    FROM [dbo].[Immigration_upload4]
),
-- 筛选出每个主体下所有符合规则的连续序列（从第一条有效记录开始，直到出现无效记录为止）
ValidSequences AS (
    SELECT 
        *,
        -- 计算当前主体下第一条无效记录的序号，用于截断无效部分
        MIN(CASE WHEN IsValid = 0 THEN RowNum ELSE NULL END) OVER (PARTITION BY PersonID) AS FirstInvalidRow
    FROM OrderedCrossings
)
SELECT 
    PersonID,
    Crossing_Direction,
    Crossing_Time,
    -- 保留其他需要的字段
    OtherFields
FROM ValidSequences
-- 保留有效范围内的记录：要么没有无效记录，要么当前行序号小于第一条无效记录
WHERE FirstInvalidRow IS NULL OR RowNum < FirstInvalidRow
-- 同时确保序列至少有一条DEPARTURE（避免空序列或起始为ARRIVAL的情况）
AND EXISTS (
    SELECT 1 
    FROM ValidSequences vs 
    WHERE vs.PersonID = ValidSequences.PersonID 
    AND vs.RowNum = 1 
    AND vs.Crossing_Direction = 'DEPARTURE'
);

关键说明： #

1. 分组与排序：必须按PersonID（或其他主体标识）分组，否则会把不同人的记录混在一起判断，这是很多LAG方案失败的常见原因。
2. 奇偶性验证：利用ROW_NUMBER()生成的序号，奇数行必须是DEPARTURE，偶数行必须是ARRIVAL，直接匹配严格交替规则。
3. 截断无效序列：如果某个主体的序列中途出现不符合规则的记录，就截断该记录及之后的所有内容，只保留前面的有效交替序列。
4. 起始校验：确保每个有效序列都是以DEPARTURE开头，避免起始为ARRIVAL的无效序列。

如果你的表没有PersonID这类主体字段，或者排序字段不是Crossing_Time，只需替换对应的字段即可。

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