sql 复杂自动编号错误批量修改方案

 

[一篮饭特稀原创，转载请注明出自http://www.cnblogs.com/wanghafan/p/5133953.html] 

 

前提：自动编号为18位，前4位是年份，中间10位是XXXX，最后四位是0001-9999的自动编号。

问题：某天发现2016年自动编号后四位从0001-0081后变为7328-8530，出现编号错乱问题(原因未知待查)。但数据需要马上批量重置，否则后续编号将继续错误。

方案：截取2016年份的前14位拼接后4位正确自动编号。实现如下：

--查询2016年我司项目编号，查看后四位是否正确
SELECT MyProjectNo,SUBSTRING(MyProjectNo,15,4) FROM dbo.ProjectT WHERE MyProjectNo LIKE '2016%' ORDER BY SUBSTRING(MyProjectNo,15,4)
--查询正确编号
SELECT MyProjectNo AS '错编号',SUBSTRING(MyProjectNo,15,4) AS '错编号后四位',LEFT(MyProjectNo,14) AS '对编号前14位',
right('0000'+CAST(row_number() over (order by SUBSTRING(MyProjectNo,15,4))  AS VARCHAR(5)),4) AS '对编号后四位',
(LEFT(MyProjectNo,14)+right('0000'+CAST(row_number() over (order by SUBSTRING(MyProjectNo,15,4))  AS VARCHAR(5)),4)) AS '对编号'
FROM dbo.ProjectT 
WHERE MyProjectNo LIKE '2016%'
ORDER BY SUBSTRING(MyProjectNo,15,4)
--更新错误编号为正确（批量脚本）
SELECT 'UPDATE dbo.ProjectT SET MyProjectNo='''+
(LEFT(MyProjectNo,14)+right('0000'+CAST(row_number() over (order by SUBSTRING(MyProjectNo,15,4))  AS VARCHAR(5)),4))
+''' WHERE MyProjectNo='''+MyProjectNo+''''
FROM dbo.ProjectT 
WHERE MyProjectNo LIKE '2016%'
ORDER BY SUBSTRING(MyProjectNo,15,4)

执行（批量脚本）实现数据重置。