如何在SQL中基于前序日期创建新列 附场景示例

这个需求核心是取排序后当前行的前一行同列值，主流支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server、Oracle、Hive等）直接用LAG()窗口函数即可实现，语法简单性能也最优。

1. 仅查询生成Col B（不需要修改原表） #

直接在查询语句中调用窗口函数即可输出你要的结果：

SELECT 
  `Col A`,
  LAG(`Col A`) OVER (ORDER BY `Col A`) AS `Col B`
FROM 你的实际表名;

说明：LAG()默认取当前行的前1行对应列值，没有前一行时默认返回NULL，完全匹配你的需求。OVER子句里的ORDER BY Col A`` 是保证按日期升序排序取前值，就算原表数据顺序混乱也不会出错。

2. 实际修改表新增Col B并填充值 #

如果需要把Col B永久加到原表中，分两步操作：

第一步：新增Col B字段 #

ALTER TABLE 你的实际表名 ADD COLUMN `Col B` VARCHAR(20);

第二步：批量填充Col B的值 #

UPDATE 你的实际表名 t1
JOIN (
  SELECT 
    `Col A`,
    LAG(`Col A`) OVER (ORDER BY `Col A`) AS pre_val
  FROM 你的实际表名
) t2 ON t1.`Col A` = t2.`Col A`
SET t1.`Col B` = t2.pre_val;

低版本数据库兼容方案（不支持窗口函数的场景） #

如果你的数据库版本太旧不支持窗口函数（比如MySQL5.7及更早版本），可以用自关联实现相同效果：

SELECT 
  t1.`Col A`,
  t2.`Col A` AS `Col B`
FROM 你的实际表名 t1
LEFT JOIN 你的实际表名 t2 ON t2.`Col A` < t1.`Col A`
LEFT JOIN 你的实际表名 t3 ON t3.`Col A` < t1.`Col A` AND t3.`Col A` > t2.`Col A`
WHERE t3.`Col A` IS NULL;

逻辑是找到比当前行Col A小的最大值，也就是最近的前一个日期值，运行结果和LAG()完全一致。

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