以下三種方式﹐實現的是同一功能﹐也是我同一天中實現 的三種方式。記載在這里不為別的﹐只為對自己一點SQL的小小總結吧。
Update TEMP_PP_SH_MFG_SCH Set MFG_SCH_JOBNO = Left(MFG_SCH_JOBNO,6)+ CASE LEN(RIGHT(MFG_SCH_JOBNO,4)+ID)
WHEN 1 THEN '000'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 2 THEN '00'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 3 THEN '0'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 4 THEN CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
END
第二種方式WHEN 1 THEN '000'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 2 THEN '00'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 3 THEN '0'+CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
WHEN 4 THEN CAST(RIGHT(MFG_SCH_JOBNO,4)+ID-1 AS VARCHAR(4))
END
Update TEMP_PP_SH_MFG_SCH Set MFG_SCH_JOBNO = Left(MFG_SCH_JOBNO,6) +
Replicate('0',4-DataLength(Cast(Right(MFG_SCH_JOBNO,4) + ID -1 As Varchar))) +
Cast(Right(MFG_SCH_JOBNO,4)+ID -1 As Varchar)
第三種方式Replicate('0',4-DataLength(Cast(Right(MFG_SCH_JOBNO,4) + ID -1 As Varchar))) +
Cast(Right(MFG_SCH_JOBNO,4)+ID -1 As Varchar)
Update TEMP_PP_SH_MFG_SCH Set MFG_SCH_JOBNO = Left(MFG_SCH_JOBNO,6)+ Right('0000'+ Cast((Cast(Right(MFG_SCH_JOBNO,4) As Int) +ID-1) As Varchar),4)
至于優劣﹐讀者朋友自然一眼就可看出。再次申明﹐放在此﹐只為算是對SQL的一點小小的總結吧。