【DB】根据某字段生成序号

如下图所示：根据RCV_ORD_NUM,生成明细行号，当RCV_ORD_NUM相同时，序号自增，不同时，从1开始并始终保持四位字符串

直接更新的方式

SET @i := 0,
@now_region_id := NULL;
UPDATE tqt_pl_prod.t_receipt_order_line l1
JOIN (
	SELECT
		ID,
		RCV_ORD_ID,
		LPAD(( @i := IF ( @now_region_id = RCV_ORD_ID, @i + 1, 1 )), 4, '0' ) RCV_ORD_LIN_NUM,
		( @now_region_id := `RCV_ORD_ID` ) 
	FROM
		tqt_pl_prod.t_receipt_order_line 
	WHERE
		( RCV_ORD_LIN_NUM IS NULL OR RCV_ORD_LIN_NUM = '' ) 
	ORDER BY
		RCV_ORD_ID,
		ID 
	) l2 ON l1.ID = l2.ID 
	SET l1.RCV_ORD_LIN_NUM = l2.RCV_ORD_LIN_NUM 
WHERE
	( l1.RCV_ORD_LIN_NUM IS NULL OR l1.RCV_ORD_LIN_NUM = '' ) 

导入数据——查询历史表插入目标表

SET @i := 0, @now_region_id:='';
INSERT INTO tqt_sp_prod.t_ship_order_line (
	ID,
	SHP_ORD_NUM,
	SHP_ORD_LIN_NUM,
	ERR_INFO
	
)SELECT
pltsol.ID,
pltsoh.SHP_ORD_NUM,
IFNULL( pltsol.SHP_ORD_LIN_NUM, LPAD( ( @i := CASE WHEN @now_region_id = pltsol.SHP_ORD_ID THEN @i + 1 ELSE 1 END ), 4, '0' ) ) SHP_ORD_LIN_NUM,
( @now_region_id := pltsol.SHP_ORD_ID )
FROM
	tqt_pl_prod.t_ship_order_line pltsol
WHERE
	(pltsoh.SHP_TYPE IS NOT NULL 
	OR pltsoh.SHP_ORD_NUM IS NOT NULL) 
	ORDER BY pltsol.ID