SQL查询问题：如何获取Group ID后续编号记录的对应Text Desc

现有数据表如下：

Group ID    Group No    Text Desc 
123A45      081         Subscriber 
123A46      083         Provider
123C48      081         Consumer
123B76      054         Worker
123B77      066         Player

需求：

• 若某条记录的Group ID存在后续编号（如123A45对应后续123A46，123B76对应后续123B77），则将后续编号对应的Text Desc赋值给当前记录的Text Desc
• 期望查询结果：

Group ID  Group No   Text Desc 
123A45       081      Provider
123B76       054      Player

此前尝试用Max条件查询未得到预期结果，需可行SQL方案。

方案1：自连接关联后续记录 #

通过字符串函数拆分Group ID的前缀与数字部分，生成后续编号后与原表自连接，直接获取后续记录的Text Desc。

MySQL 版本：

SELECT 
    t1.`Group ID`,
    t1.`Group No`,
    t2.`Text Desc`
FROM 
    your_table t1
JOIN 
    your_table t2 ON 
        CONCAT(LEFT(t1.`Group ID`, LENGTH(t1.`Group ID`) - 2), 
               LPAD(CAST(RIGHT(t1.`Group ID`, 2) AS UNSIGNED) + 1, 2, '0')) = t2.`Group ID`;

SQL Server 版本：

SELECT 
    t1.[Group ID],
    t1.[Group No],
    t2.[Text Desc]
FROM 
    your_table t1
JOIN 
    your_table t2 ON 
        CONCAT(LEFT(t1.[Group ID], LEN(t1.[Group ID]) - 2), 
               RIGHT('00' + CAST(CAST(RIGHT(t1.[Group ID], 2) AS INT) + 1 AS VARCHAR(2)), 2)) = t2.[Group ID];

方案2：窗口函数 LEAD #

利用LEAD窗口函数，按Group ID的前缀分组、数字部分排序，直接获取下一条记录的Text Desc，再筛选出存在后续记录的行。

MySQL 版本：

SELECT 
    `Group ID`,
    `Group No`,
    next_text_desc AS `Text Desc`
FROM (
    SELECT 
        *,
        LEAD(`Text Desc`) OVER (
            PARTITION BY LEFT(`Group ID`, LENGTH(`Group ID`) - 2) 
            ORDER BY CAST(RIGHT(`Group ID`, 2) AS UNSIGNED)
        ) AS next_text_desc
    FROM your_table
) t
WHERE next_text_desc IS NOT NULL;

SQL Server 版本：

SELECT 
    [Group ID],
    [Group No],
    next_text_desc AS [Text Desc]
FROM (
    SELECT 
        *,
        LEAD([Text Desc]) OVER (
            PARTITION BY LEFT([Group ID], LEN([Group ID]) - 2) 
            ORDER BY CAST(RIGHT([Group ID], 2) AS INT)
        ) AS next_text_desc
    FROM your_table
) t
WHERE next_text_desc IS NOT NULL;

说明 #

两种方案的核心都是识别Group ID的后续编号关联关系：

• 自连接方案逻辑直观，适合需要明确关联规则的场景
• LEAD窗口函数方案代码更简洁，适合按顺序取后续记录的场景

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