怎么写向不是自增列的字段里插入从某个数字开始递增的SQL语句,谢
如何写向不是自增列的字段里插入从某个数字开始递增的SQL语句,谢~
如图所示,我想把字段nh=2008的数据重新排列swh,使swh以200800001开始向下递增,而其他年份的数据保持不变,swh为Varchar(255)类型,谢~
------解决方案--------------------
如图所示,我想把字段nh=2008的数据重新排列swh,使swh以200800001开始向下递增,而其他年份的数据保持不变,swh为Varchar(255)类型,谢~
------解决方案--------------------
- SQL code
update a set swh = '2008'+ right('00000'+ltrim((select count(1) from tablename where nh = '2008'id <= a.id)),5) from talbename a where nh = '2008'
------解决方案--------------------
- SQL code
CREATE TABLE TB(ID INT, nh INT, swh VARCHAR(20)) INSERT INTO TB VALUES(582 , 2007, '200700001') INSERT INTO TB VALUES(583 , 2007, '200700002') INSERT INTO TB VALUES(7710, 2008, '200800001') INSERT INTO TB VALUES(7768, 2008, '200800001') INSERT INTO TB VALUES(7769, 2008, '200800001') INSERT INTO TB VALUES(7842, 2008, '200800001') INSERT INTO TB VALUES(7845, 2008, '200800001') INSERT INTO TB VALUES(7889, 2008, '200800001') INSERT INTO TB VALUES(7912, 2008, '200800001') INSERT INTO TB VALUES(7963, 2008, '200800001') GO --查询 SELECT * FROM TB WHERE NH <> 2008 UNION ALL SELECT ID , NH , SWH = '2008' + RIGHT('00000' + CAST(M.SWH AS VARCHAR),5) FROM ( SELECT ID , NH , SWH = (SELECT COUNT(1) FROM TB WHERE NH = 2008 AND ID < T.ID) + 1 FROM TB T WHERE NH = 2008 ) M /* ID nh swh ----------- ----------- -------------------- 582 2007 200700001 583 2007 200700002 7710 2008 200800001 7768 2008 200800002 7769 2008 200800003 7842 2008 200800004 7845 2008 200800005 7889 2008 200800006 7912 2008 200800007 7963 2008 200800008 */ --更改 update tb set swh = t2.swh from tb t1 , (SELECT ID , NH , SWH = '2008' + RIGHT('00000' + CAST(M.SWH AS VARCHAR),5) FROM ( SELECT ID , NH , SWH = (SELECT COUNT(1) FROM TB WHERE NH = 2008 AND ID < T.ID) + 1 FROM TB T WHERE NH = 2008 ) M) t2 where t1.id = t2.id select * from tb /* ID nh swh ----------- ----------- -------------------- 582 2007 200700001 583 2007 200700002 7710 2008 200800001 7768 2008 200800002 7769 2008 200800003 7842 2008 200800004 7845 2008 200800005 7889 2008 200800006 7912 2008 200800007 7963 2008 200800008 */ DROP TABLE TB