SQL中按GATEVISITNUMBER分组获取首/后续SUB_TYPE值的方法

安全阿岳

2026-6-21

解决方案

问题出在你使用LAG()时没有指定分区（PARTITION BY），导致窗口函数跨GATEVISITNUMBER取值。要实现需求，核心是按GATEVISITNUMBER分组，再对组内数据按TIME排序，提取第一个和后续的SUB_TYPE（示例中为第二个，若有更多后续可扩展）。

方法1：窗口函数+条件聚合

先给每个GATEVISITNUMBER下的记录按时间排序编号，再通过聚合函数提取对应位置的SUB_TYPE：

SELECT
    GATEVISITNUMBER,
    MAX(CASE WHEN rn = 1 THEN SUB_TYPE END) AS SUB_TYPE,
    MAX(CASE WHEN rn = 2 THEN SUB_TYPE END) AS SUB_TYPE2
FROM (
    SELECT
        GATEVISITNUMBER,
        SUB_TYPE,
        TIME,
        ROW_NUMBER() OVER (PARTITION BY GATEVISITNUMBER ORDER BY TIME) AS rn
    FROM your_table_name
) t
GROUP BY GATEVISITNUMBER;

PARTITION BY GATEVISITNUMBER确保窗口函数只在同一个GATEVISITNUMBER的范围内计算
ROW_NUMBER()按时间给组内记录排序编号
外层用MAX()+CASE提取第1、2位的SUB_TYPE，没有对应位置时自动返回NULL

方法2：修正窗口函数用法（提取组内下一条数据）

如果坚持用窗口函数直接获取后续值，LEAD()比LAG()更适配需求，结合分区和过滤只保留每组第一条记录：

SELECT
    GATEVISITNUMBER,
    SUB_TYPE,
    next_sub_type AS SUB_TYPE2
FROM (
    SELECT
        GATEVISITNUMBER,
        SUB_TYPE,
        LEAD(SUB_TYPE) OVER (PARTITION BY GATEVISITNUMBER ORDER BY TIME) AS next_sub_type,
        ROW_NUMBER() OVER (PARTITION BY GATEVISITNUMBER ORDER BY TIME) AS rn
    FROM your_table_name
) t
WHERE rn = 1;