--SQL2005+的方法 ;WITH CTE AS( SELECT bm,bm_lb+RIGHT('00'+CAST(ROW_NUMBER()OVER(ORDER BY GETDATE())AS VARCHAR(10)),3)RN FROM TB ) UPDATE CTE SET bm=RN--SQL2000的话比较麻烦 --建议如下 SELECT IDENTITY(INT,1,1)RN,* INTO #T FROM TB UPDATE T1 SET bm=T1.bm_lb+RIGHT('00'+CAST(T2.RN AS VARCHAR(10)),3) FROM TB T1 JOIN #T T2 ON T1.bm=T2.bm DROP TABLE #T
;WITH CTE AS(
SELECT bm,bm_lb+RIGHT('00'+CAST(ROW_NUMBER()OVER(ORDER BY GETDATE())AS VARCHAR(10)),3)RN
FROM TB
)
UPDATE CTE
SET bm=RN--SQL2000的话比较麻烦
--建议如下
SELECT IDENTITY(INT,1,1)RN,* INTO #T FROM TB
UPDATE T1
SET bm=T1.bm_lb+RIGHT('00'+CAST(T2.RN AS VARCHAR(10)),3)
FROM TB T1
JOIN #T T2 ON T1.bm=T2.bm
DROP TABLE #T