如何在SQL中为同表添加基于ITEM和DATE的下一个状态列

我有如下结构的表：

希望新增一列NEW STATUS，该列根据ITEM名称和DATE，展示对应ITEM的下一个状态，预期结果如下：

请问如何用SQL实现该需求？

可以使用SQL的**窗口函数LEAD()**来实现这个需求，LEAD()函数能在同一分组内，获取当前行之后指定位置的行数据。

核心SQL语句 #

SELECT 
    ITEM,
    DATE,
    STATUS,
    -- 拼接状态和日期说明，无后续状态则返回空
    CASE 
        WHEN LEAD(STATUS) OVER (PARTITION BY ITEM ORDER BY STR_TO_DATE(DATE, '%d.%m.%Y')) IS NOT NULL 
        THEN CONCAT(
            LEAD(STATUS) OVER (PARTITION BY ITEM ORDER BY STR_TO_DATE(DATE, '%d.%m.%Y')),
            '（基于ITEM ', ITEM, '在', DATE_FORMAT(LEAD(STR_TO_DATE(DATE, '%d.%m.%Y')) OVER (PARTITION BY ITEM ORDER BY STR_TO_DATE(DATE, '%d.%m.%Y')), '%Y-%m-%d'), '的状态）'
        )
        ELSE ''
    END AS `NEW STATUS`
FROM 
    your_table_name; -- 替换为你的实际表名

语句说明 #

1. PARTITION BY ITEM：按ITEM分组，确保仅在同一ITEM范围内查找下一个状态
2. ORDER BY STR_TO_DATE(DATE, '%d.%m.%Y')：将字符串格式的日期转换为日期类型后排序，避免字符串排序导致的顺序错误
3. LEAD()函数：分别获取下一行的状态和转换后的日期，用于拼接成要求的格式
4. CASE判断：如果当前是该ITEM的最后一条记录（无后续状态），则返回空字符串，否则拼接状态与日期说明

注意事项 #

• 若DATE字段本身是日期类型，可去掉STR_TO_DATE和DATE_FORMAT转换，直接使用DATE字段排序和拼接
• 不同数据库的日期转换函数有差异：
  • MySQL：STR_TO_DATE/DATE_FORMAT
  • PostgreSQL：TO_DATE/TO_CHAR
  • SQL Server：CONVERT

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