Id, FormatId, F1 ,F2 Id序号我设了自动加一,FormatId我想他也象这样"SL000001", 当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"... 能用一条sql什么办法实现.最好不要用中间表。有什么好方法? 谢谢! create table #test (id int identity, FormatId as 'SL'+right(10000000+id,6), F1 varchar(50)) go insert #test(F1) select '1' union all select '2' select * from #testdrop table #test /* id FormatId F1 ----------- -------------- ----- 1 SL000001 1 2 SL000002 2(所影响的行数为 2 行) */
Create Table TEST (Code Varchar(10), name Nvarchar(20)) Insert TEST(name) Select N'笔' Union All Select N'纸' Union All Select N'书本' GO Update A Set Code = 'CNPA' + Right(1000000 + (Select Count(*) From TEST Where name <= A.name), 6) From TEST ASelect * From TEST Order By Code GO Drop Table TEST --Result /* Code name CNPA000001 书本 CNPA000002 笔 CNPA000003 纸 */
create table #test(id int identity , Code as 'CNPA'+right(10000000+id,6),name varchar(10)) insert into #test(name) values('笔') insert into #test(name) values('纸') insert into #test(name) values('书本')select * from #testdrop table #test/* id Code name ----------- ---------------- ---------- 1 CNPA000001 笔 2 CNPA000002 纸 3 CNPA000003 书本(所影响的行数为 3 行) */
Id序号我设了自动加一,FormatId我想他也象这样"SL000001",
当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"...
能用一条sql什么办法实现.最好不要用中间表。有什么好方法?
谢谢!
create table #test
(id int identity,
FormatId as 'SL'+right(10000000+id,6),
F1 varchar(50))
go
insert #test(F1) select '1'
union all select '2'
select * from #testdrop table #test
/*
id FormatId F1
----------- -------------- -----
1 SL000001 1
2 SL000002 2(所影响的行数为 2 行)
*/
(Code Varchar(10),
name Nvarchar(20))
Insert TEST(name) Select N'笔'
Union All Select N'纸'
Union All Select N'书本'
GO
Update A Set Code = 'CNPA' + Right(1000000 + (Select Count(*) From TEST Where name <= A.name), 6) From TEST ASelect * From TEST Order By Code
GO
Drop Table TEST
--Result
/*
Code name
CNPA000001 书本
CNPA000002 笔
CNPA000003 纸
*/
insert into #test(name) values('笔')
insert into #test(name) values('纸')
insert into #test(name) values('书本')select * from #testdrop table #test/*
id Code name
----------- ---------------- ----------
1 CNPA000001 笔
2 CNPA000002 纸
3 CNPA000003 书本(所影响的行数为 3 行)
*/
如过有主键的,采用计算列方式也可以(主键是自增int类型),那么就不需要修改任何源程序
在表的设计页面中选择该字段,该字段属性有“公式”,输入 ('CNPA' + right((10000000 + [id]),6))即可