sql server 怎么填加数字加日期的自动编码方式 找了好就没有找到

[nwznwz]

例如有个表，有2个属性分别是a和b，希望往a中填加数据的时候，系统会自动给a形成系统时间+数字的编码
就像2008031001，给b填加第2条的时候，a就会形成2008031002的属性
不过当到第2个月的时候就变成2008040101开始了，如此循环，希望高手能指导

[cxmcxm]

通过插入触发器来实现
取日期的函数为getdate()
declare @str char(6),@newfld char(10)
select @str=convert(char(6),getdate(),112) --取当前年月
select top 1 @newfld=f1 from tb where f1 like @str+'%' order by f1 desc --高要生成的字段为f1,表为tb
if @newfld is null
set @newfld=@str+'0001'
else
set @newfld=@str+right(convert(char(5),10001+convert(int,right(@newfld,4))),4)

可取得最大值

[wzy_love_sly]

oo

[wzy_love_sly]

create table tb(id varchar(20) default dbo.getid(),name varchar(20))

create view dt
as
select getdate() as dt


create function getid()
returns varchar(20)
as
begin
declare @id varchar(20),@dt datetime
select @dt=dt from dt
select @id=replace(convert(varchar(10),@dt,120),'-','')+ltrim(isnull(max(cast(right(id,len(id)-7) as int)),0)+1) from tb
return @id
end

insert into tb (name) select 'aa'
insert into tb (name) select 'bb'
insert into tb (name) select 'cc'
insert into tb (name) select 'dd'
insert into tb (name) select 'ee'
insert into tb (name) select 'ff'
insert into tb (name) select 'gg'
insert into tb (name) select 'hh'
insert into tb (name) select 'ii'
insert into tb (name) select 'jj'
insert into tb (name) select 'kk'
insert into tb (name) select 'll'
insert into tb (name) select 'mm'
insert into tb (name) select 'nn'

select * from tb

200803101	aa
200803102	bb
200803103	cc
200803104	dd
200803105	ee
200803106	ff
200803107	gg
200803108	hh
200803109	ii
2008031010	jj
2008031011	kk
2008031012	ll
2008031013	mm
2008031014	nn

[zbc1009]

楼上正解

[nwznwz]

想请问下1楼的，那个触发器应该怎么设置呢，在你插入 b的时候，a会自动的显示你编的@newfld呢

[nwznwz]

我那3楼的程序编写了，可是显示的时候出了问题呢
变成了
200803111 aa
2008031112 bb
20080311113 cc
200803111114 dd
2008031111115 ee
20080311111116 ff
200803111111117 gg
2008031111111118 hh
20080311111111119 ii
200803111111111120 jj
不知道是什么问题

[leisure_cool]

declare @table table(num varchar(20),name varchar(20))
declare @begin varchar(10)
declare @y int
declare @m int
declare @d int
set @m=month(getdate())
set @y=year(getdate())
if ((@y%4=0 and @y%100 !=0) or (@y%400=0))
	begin
		if (@m=2)
			set @d=29
		else if (@m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12)
			set @d=31
		else
			set @d=30
	end
else
	begin
		if (@m=2)
			set @d=28
		else if (@m=1 or @m=3 or @m=5 or @m=7 or @m=8 or @m=10 or @m=12)
			set @d=31
		else
			set @d=30
	end

set @begin=convert(char(4),@y)+'-'+convert(char(2),@m)+'-01'
declare @num varchar(20)
declare @i int
declare @ii char(2)
set @i=0
while @i <@d
	begin
		begin
		if @i<10
		set @num=rtrim(convert(char(11),dateadd(day,@i,convert(datetime,@begin)),112))+'0'+convert(char,@i)
		else
		set @num=rtrim(convert(char(11),dateadd(day,@i,convert(datetime,@begin)),112))+convert(char,@i)
		end
	    insert into @table
		select @num,@i
		set @i=@i+1
	end
	
select * from @table
/*
   num                 name
-------------------------
2008030100          	0
2008030201          	1
2008030302          	2
2008030403          	3
2008030504          	4
2008030605          	5
2008030706          	6
2008030807          	7
2008030908          	8
2008031009          	9
2008031110          	10
2008031211          	11
2008031312          	12
2008031413          	13
2008031514          	14
2008031615          	15
2008031716          	16
2008031817          	17
2008031918          	18
2008032019          	19
2008032120          	20
2008032221          	21
2008032322          	22
2008032423          	23
2008032524          	24
2008032625          	25
2008032726          	26
2008032827          	27
2008032928          	28
2008033029          	29
2008033130          	30
*/

[nwznwz]

那这样的话只能循环+1来保持编码加1啊？
假如我同一天要有2个编号来区别呢？
2008031401
2008031402
呢？我主要的目的就是要在插入name的时候，自动插入num，
2楼的程序能满足要求，关键就是插入触发器只能一行一行的插，怎么只查单行的单个属性呢？