是啊,int型的前面的0会被去掉的要不你还是保存1,2,3... 显示的时候用: select right('0000'+订单号,4) from 表
自动增长列只能是int类型, 建议建立视图: Create View view1 As Select 订单号=right('0000'+饅convert(varchar(20),订单号),4),* from table
--转帖: --自已做标识列的例子--流水号:日期+当日编号:--创建得到最大id的函数 create function f_getid() returns varchar(13) as begin declare @id varchar(13),@dt varchar(8) select @dt=dt from v_getdate select @id=max(id) from tb where id like @dt+'-%' if @id is null set @id=@dt+'-'+'0001' else set @id=@dt+'-'+right('0000'+cast(cast(right(@id,4) as int)+1 as varchar),4) return(@id) end go--创建表 create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10)) go--创建视图,得到当前日期(因为函数中不能使用getdate()) create view v_getdate as select dt=convert(varchar,getdate(),112) go--插入记录测试 insert into tb(name) values('张三') insert into tb(name) values('张四') insert into tb(name) values('张五') insert into tb(name) values('张六') insert into tb(name) values('张七') insert into tb(name) values('张八') insert into tb(name) values('张九') insert into tb(name) values('张十')--显示插入的结果 select * from tb--删除部分记录 delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果 select * from tb go--删除环境 drop table tb drop view v_getdate drop function f_getid
显示的时候用:
select right('0000'+订单号,4) from 表
Create View view1
As
Select 订单号=right('0000'+饅convert(varchar(20),订单号),4),*
from table
--自已做标识列的例子--流水号:日期+当日编号:--创建得到最大id的函数
create function f_getid()
returns varchar(13)
as
begin
declare @id varchar(13),@dt varchar(8)
select @dt=dt from v_getdate
select @id=max(id) from tb where id like @dt+'-%'
if @id is null
set @id=@dt+'-'+'0001'
else
set @id=@dt+'-'+right('0000'+cast(cast(right(@id,4) as int)+1 as varchar),4)
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt=convert(varchar,getdate(),112)
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb
go--删除环境
drop table tb
drop view v_getdate
drop function f_getid