怎么写向不是自增列的字段里插入从某个数字开始递增的SQL语句，谢
日期：2014-05-18 浏览次数：20566 次
如何写向不是自增列的字段里插入从某个数字开始递增的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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
