declare @t table(code varchar(10))
declare @i int
set @i=1while @i<10
begin
insert into @t(code)
select
convert(char(8),getdate(),112)+right('0'+rtrim(isnull(max(right(code,2)),0)+1),2)
from
@t
where
left(code,8)=convert(char(8),getdate(),112)
set @i=@i+1
endselect * from @t/*
code
----------
2006110901
2006110902
2006110903
2006110904
2006110905
2006110906
2006110907
2006110908
2006110909
*/
declare @i int
set @i=1while @i<10
begin
insert into @t(code)
select
convert(char(8),getdate(),112)+right('0'+rtrim(isnull(max(right(code,2)),0)+1),2)
from
@t
where
left(code,8)=convert(char(8),getdate(),112)
set @i=@i+1
endselect * from @t/*
code
----------
2006110901
2006110902
2006110903
2006110904
2006110905
2006110906
2006110907
2006110908
2006110909
*/
insert into @t(date) select '2006-11-09 14:20:00.000'
insert into @t(date) select '2006-11-09 14:23:00.000'
insert into @t(date) select '2006-11-09 14:25:00.000'
insert into @t(date) select '2006-11-09 14:26:00.000'declare @i int
set @i=0
update @t
set id=convert(char(08),date,112)+right(100+@i,2)
,@i=@i+1--
select id from @t
iceno,goods,date,num我要查找数据表中的同一天的数据(date)且按相同的iceno号在后面按时间增加一列,如:2006110901,后面两位为区分不同iceno号所生成的不同序号。
如:
1000,xx,2006-11-9,1,2006110901
1000,yy,2006-11-9,1,2006110901
1000,zz2006-11-9,2,2006110901
1001,xx,2006-11-9,1,2006110902
1001,hh,2006-11-9,2,2006110902
1002,hh,2006-11-9,1,2006110903
1002,xx,2006-11-9,1,2006110903