ID nh swh tm
582 2007 200700001 关于...
583 2007 200700002 关于...
7710 2008 200800001 关于...
7768 2008 200800001 关于...
7769 2008 200800001 关于...
7842 2008 200800001 关于...
7845 2008 200800001 关于...
7889 2008 200800001 关于...
7912 2008 200800001 关于...
7963 2008 200800001 关于...如果看不到图的话,大致表如上,谢~
582 2007 200700001 关于...
583 2007 200700002 关于...
7710 2008 200800001 关于...
7768 2008 200800001 关于...
7769 2008 200800001 关于...
7842 2008 200800001 关于...
7845 2008 200800001 关于...
7889 2008 200800001 关于...
7912 2008 200800001 关于...
7963 2008 200800001 关于...如果看不到图的话,大致表如上,谢~
set swh = '2008'+ right('00000'+ltrim((select count(1) from tablename where nh = '2008'id <= a.id)),5)
from talbename a
where nh = '2008'
“查询设计器不支持 Optional FROM clause SQL 构造。”
SET swh = '2008' + RIGHT('00000' + CAST(? AS varchar), 5)
WHERE (nh = '2008')这?里面应该怎么写,请教大家了
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
SET swh = '2007' + RIGHT('00000' + CAST(ID AS varchar), 5) - 1000
WHERE (nh = '2007')我已通过按照ID自增列的方式解决了swh的自增,我想这也是最简便的方法了,结贴,感谢大家的帮助~dawugui,happyflystone 的方法正解