SQLserver自定义序列号

这是原本的spl代码

 car_weight =  B1.query("
	SELECT
	DISTINCT
		LTD1.*,
		(
		SELECT TOP 1 tmi.mix_grade_str 
			AS material_type_in 
		FROM
			[RTPC].[dbo].tbl_material_info tmi 
		WHERE
			material_name = LTD51.MatCode COLLATE Chinese_PRC_90_CI_AI_WS 
			and company_code = ?
		ORDER BY create_time DESC	
		) AS material_type_in,
		( SELECT TOP 1 material_class_small FROM [RTPC].[dbo].tbl_material_info tmi WHERE material_name = LTD51.MatCode COLLATE Chinese_PRC_90_CI_AI_WS
		and  company_code = ?
		ORDER BY create_time DESC ) AS material_class_small_in,
		LTD51.MatCode AS material_code_in,
		LTD51.MatName AS material_name_in,
		LTD51.ReqWT,
		LTD51.ActWT,
		LTD51.ActWT AS weight_in
	FROM
		(
			SELECT
			    plan_id_out,
			    lot_id_out,
			    material_type_out,
			    pro_date_out,
			    shift_id_out,
			    equip_id_out,
			    material_code_out,
			    material_name_out,
			    unit_cost_out,
			    weight_out,
			    cost_out,
			    batchno_out as batchno_out_old,
			    ROW_NUMBER() OVER(PARTITION BY plan_id_out ORDER BY plan_id_out,batchno_out) AS batchno_out
			FROM
			    (
			        SELECT
			            ORDERNO AS plan_id_out,
			            LOTID AS lot_id_out,
			            (
			                SELECT TOP 1 tmi.mix_grade_str AS material_type 
			                FROM [RTPC].[dbo].tbl_material_info tmi 
			                WHERE material_name = ITNBR COLLATE Chinese_PRC_90_CI_AI_WS 
			                and company_code = ?
			                ORDER BY create_time DESC	
			            ) AS material_type_out,
			            JDAT AS pro_date_out,
			            CAST ( JSHT AS INT ) AS shift_id_out,
			            JMCH AS equip_id_out,
			            ITNBR AS material_code_out,
			            ITDSC AS material_name_out,
			            '' AS unit_cost_out,
			            jwt AS weight_out,
			            '' AS cost_out,
			            CAST ( VALUE AS INT ) AS batchno_out 
			        FROM
			            PLMES.dbo.LTD0001 CROSS APPLY STRING_SPLIT ( CNUM, ',' ) 
			        WHERE
			           JDAT = ?
			           AND JSHT = ?
			            and  DIV <> 'XL'
			    ) AS subquery
		) LTD1
		LEFT JOIN PLMES.dbo.LTD0051 LTD51 ON LTD1.plan_id_out = LTD51.PlanId 
		AND LTD1.batchno_out = LTD51.BatchNo
		INNER JOIN PLMES.dbo.PPB0103 ppb3 ON LTD1.plan_id_out = ppb3.ORDERNO 
		where ppb3.STATE <> '4' 
		AND ppb3.STATE <> '5' 

",COMPANY_CODE,COMPANY_CODE,COMPANY_CODE,PRO_DATE,PRO_SHIFT)
.sort(plan_id_out,batchno_out)

需求

车次号不正确，需要根据得到的计划号，重新排序一下
例如：车次显示的是4、5、6、7，需要重排成1、2、3、4

语法

• ROW_NUMBER() OVER 重新编号
• PARTITION BY 基于什么分组
• ORDER BY 基于什么排序

ROW_NUMBER() OVER(
PARTITION BY plan_id_out ORDER BY plan_id_out,batchno_out
) AS batchno_out