如何在自连接表中匹配同一Job和Sfx的下一个最小Seq

我有一张包含Job #、Job Suffix和Job Sequence的表，需将该表与自身连接，获取同一Job和Job Sfx对应的下一个Sequence。例如Job为00001、Sfx为001、Seq为100时，希望匹配到对应的下一个Seq（示例为200，但不固定），结果展示为00001、001、100、200。

当前查询语句 #

select a.job, a.sfx, a.seq, b.seq 
from a 
left join b 
on a.job=b.job and a.sfx=b.sfx 
where a.seq < b.seq

（注：原语句中b.seq后多了一个语法错误的逗号，已修正）

示例表数据 #

当前查询结果 #

我希望每个Seq仅匹配到比它大的最小Seq（如Seq200匹配300，Seq300无匹配），而非所有更大的Seq，请求解决方案。

解决方案 #

方法1：使用窗口函数LEAD()（推荐） #

这是最简洁高效的实现方式，LEAD()函数可直接获取同一分组内的下一行数据：

SELECT 
    job, 
    sfx, 
    seq, 
    LEAD(seq) OVER (PARTITION BY job, sfx ORDER BY seq) AS next_seq
FROM 
    your_table_name;

执行结果：

方法2：使用子查询筛选最小的更大Seq #

如果你的数据库不支持窗口函数，可用子查询定位每个Seq对应的最小更大值：

SELECT 
    a.job, 
    a.sfx, 
    a.seq, 
    (SELECT MIN(b.seq) 
     FROM your_table_name b 
     WHERE b.job = a.job 
       AND b.sfx = a.sfx 
       AND b.seq > a.seq) AS next_seq
FROM 
    your_table_name a;

方法3：使用JOIN结合聚合函数 #

通过自连接后分组取最小值，也能实现需求：

SELECT 
    a.job, 
    a.sfx, 
    a.seq, 
    MIN(b.seq) AS next_seq
FROM 
    your_table_name a
LEFT JOIN 
    your_table_name b ON a.job = b.job 
                     AND a.sfx = b.sfx 
                     AND b.seq > a.seq
GROUP BY 
    a.job, a.sfx, a.seq;

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