create table tb (tid int identity, id as right('00'+cast(tid as varchar),3), name varchar(10)) goinsert into tb (name) values('aaa') insert into tb (name) values('bbb') insert into tb (name) values('ccc') select * From tbdrop table tb/* tid id name ----------- ------ ---------- 1 001 aaa 2 002 bbb 3 003 ccc(所影响的行数为 3 行) */ 不过,你三位貌似太少了,自己加大位数.
在学习中遇到这个问题 数据库里有编号字段 BH00001 BH00002 BH00003 BH00004 如何实现自动增长 --下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。 --得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END GO--在表中应用函数 CREATE TABLE tb( BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 BEGIN TRAN INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14) COMMIT TRAN--显示结果 SELECT * FROM tb /*--结果 BH col ---------------- ----------- BH000001 1 BH000002 2 BH000003 4 BH000004 14 --*/ create table tb (id int identity, name varchar(10), code as 'BH'+right('0000'+cast(id as varchar),5)) go insert tb(name) select 'A' union all select 'B' union all select 'C' union all select 'D'select * from tbdrop table tb/* id name code ----------- ---------- ------------ 1 A BH00001 2 B BH00002 3 C BH00003 4 D BH00004(所影响的行数为 4 行) */
CREATE TABLE Test ( id CHAR(3) NOT NULL primary key , name NVARCHAR(50) )DECLARE @i int DECLARE @id CHAR(3) DECLARE @name NVARCHAR(50) = '输入值?'--查询最大值 + 1 ,没有是1 SELECT @i = ISNULL(MAX(id),0) + 1 FROM Test --生成ID SET @id = Right(replicate('0',3) + convert(varchar,@i),3) --插入值 INSERT INTO Test VAlUES(@id, @name)
create table tb (tid int identity, id as right('000'+cast(tid as varchar),4), name varchar(10)) goinsert into tb (name) values('aaa') insert into tb (name) values('bbb') insert into tb (name) values('ccc') select * From tbdrop table tb/* tid id name ----------- ------ ---------- 1 001 aaa 2 002 bbb 3 003 ccc(所影响的行数为 3 行) */ 能够自增0001 id as right('000'+cast(tid as varchar),4),修改这句OK
将id设为自增的,直插入name就行了!
if object_id('tb') is not null drop table tb go create table tb ( id nvarchar(10), name nvarchar(10), time1 datetime default(getdate()) )alter proc Inserttb @name nvarchar(10) as begin declare @Count int set @Count=cast((select count(*) from tb) as int) if @Count=0 begin insert into tb(id,name) values('001',@name) end else begin declare @max nvarchar(10) set @max=cast(( select top 1 id from tb order by time1 desc) as nvarchar) insert into tb(id,name) values( right( ('000'+ cast(( cast(( substring(@max,(patindex('%[1-9]%',@max)),(len(@max)-patindex('%[1-9]%',@max) +1))) as int)+1 ) as nvarchar)),3) ,@name) end end 结果: 001 qqq 2010-01-04 09:03:17.670 002 qqq 2010-01-04 09:03:17.840 003 qqq 2010-01-04 09:03:18.030 004 qqq 2010-01-04 09:03:18.187 005 qqq 2010-01-04 09:03:18.373 006 qqq 2010-01-04 09:03:18.543 007 qqq 2010-01-04 09:03:18.733 008 qqq 2010-01-04 09:03:18.903 009 qqq 2010-01-04 09:03:26.700 010 qqq 2010-01-04 09:03:26.890 011 qqq 2010-01-04 09:03:27.077 012 qqq 2010-01-04 09:03:27.247 013 qqq 2010-01-04 09:03:27.420 014 qqq 2010-01-04 09:03:27.607 015 qqq 2010-01-04 09:03:27.780
(tid int identity,
id as right('00'+cast(tid as varchar),3),
name varchar(10))
goinsert into tb (name) values('aaa')
insert into tb (name) values('bbb')
insert into tb (name) values('ccc') select * From tbdrop table tb/*
tid id name
----------- ------ ----------
1 001 aaa
2 002 bbb
3 003 ccc(所影响的行数为 3 行)
*/
不过,你三位貌似太少了,自己加大位数.
数据库里有编号字段
BH00001
BH00002
BH00003
BH00004
如何实现自动增长 --下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/ create table tb
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'select * from tbdrop table tb/*
id name code
----------- ---------- ------------
1 A BH00001
2 B BH00002
3 C BH00003
4 D BH00004(所影响的行数为 4 行)
*/
(
id CHAR(3) NOT NULL primary key
, name NVARCHAR(50)
)DECLARE @i int
DECLARE @id CHAR(3)
DECLARE @name NVARCHAR(50) = '输入值?'--查询最大值 + 1 ,没有是1
SELECT @i = ISNULL(MAX(id),0) + 1 FROM Test
--生成ID
SET @id = Right(replicate('0',3) + convert(varchar,@i),3)
--插入值
INSERT INTO Test
VAlUES(@id, @name)
(tid int identity,
id as right('000'+cast(tid as varchar),4),
name varchar(10))
goinsert into tb (name) values('aaa')
insert into tb (name) values('bbb')
insert into tb (name) values('ccc') select * From tbdrop table tb/*
tid id name
----------- ------ ----------
1 001 aaa
2 002 bbb
3 003 ccc(所影响的行数为 3 行)
*/
能够自增0001
id as right('000'+cast(tid as varchar),4),修改这句OK
drop table tb
go
create table tb
(
id nvarchar(10),
name nvarchar(10),
time1 datetime default(getdate())
)alter proc Inserttb
@name nvarchar(10)
as
begin
declare @Count int
set @Count=cast((select count(*) from tb) as int)
if @Count=0
begin
insert into tb(id,name)
values('001',@name)
end
else
begin
declare @max nvarchar(10)
set @max=cast(( select top 1 id from tb order by time1 desc) as nvarchar)
insert into tb(id,name)
values( right(
('000'+
cast(( cast(( substring(@max,(patindex('%[1-9]%',@max)),(len(@max)-patindex('%[1-9]%',@max) +1))) as int)+1 ) as nvarchar)),3) ,@name)
end
end
结果:
001 qqq 2010-01-04 09:03:17.670
002 qqq 2010-01-04 09:03:17.840
003 qqq 2010-01-04 09:03:18.030
004 qqq 2010-01-04 09:03:18.187
005 qqq 2010-01-04 09:03:18.373
006 qqq 2010-01-04 09:03:18.543
007 qqq 2010-01-04 09:03:18.733
008 qqq 2010-01-04 09:03:18.903
009 qqq 2010-01-04 09:03:26.700
010 qqq 2010-01-04 09:03:26.890
011 qqq 2010-01-04 09:03:27.077
012 qqq 2010-01-04 09:03:27.247
013 qqq 2010-01-04 09:03:27.420
014 qqq 2010-01-04 09:03:27.607
015 qqq 2010-01-04 09:03:27.780