27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT number rn,
'H'+RIGHT('00000000'+CONVERT(varchar(8),number),8) new_sheetid
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 90 AND 110
rn new_sheetid
----------- -----------------
90 H00000090
91 H00000091
92 H00000092
93 H00000093
94 H00000094
95 H00000095
96 H00000096
97 H00000097
98 H00000098
99 H00000099
100 H00000100
101 H00000101
102 H00000102
103 H00000103
104 H00000104
105 H00000105
106 H00000106
107 H00000107
108 H00000108
109 H00000109
110 H00000110
CREATE TABLE #T(
rn int identity,
sdate datetime,
old_id varchar(9),
new_id varchar(9)
)
INSERT INTO #t(sdate,old_id)
SELECT sdate, id
FROM table1
ORDER BY sdate
UPDATE #t
SET new_id = 'H'+RIGHT('00000008'+CONVERT(varchar(8),rn),8)
UPDATE table1
SET table1.ID = #t.new_id
FROM table1, #t
WHERE table1.ID = #t.old_id
select 'H'+ substring(cast(10000000+ ROW_NUMBER() OVER(order by Sdate) as char(8)),2,7),Sdate
from t
order by Sdate