declare @t table (fid int,fdate smalldatetime,fother varchar(1000))
declare @id int,@d smalldatetime
set @id=123456
set @d='2009-1-2'

set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @d=@d+1
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @d=@d+1
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @d=@d+1
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())
set @d=@d+1
set @id=@id+1;insert @t values (@id,@d,rand())
set @id=@id+1;insert @t values (@id,@d,rand())

--列出测试数据
select * from @t

--列出含编号的记录
select convert(varchar(100),a.fdate,112)+right('000'+cast(1+a.fid-b.fid as varchar(3)),3) fno, a.* from @t a
left join (select fdate,min(fid) fid from @t group by fdate) b
on a.fdate=b.fdate

fid fdate fother
123457 2009-01-02 00:00:00 0.669186
123458 2009-01-02 00:00:00 0.770396
123459 2009-01-02 00:00:00 0.659152
123460 2009-01-02 00:00:00 0.129873
123461 2009-01-03 00:00:00 0.0639277
123462 2009-01-03 00:00:00 0.835485
123463 2009-01-03 00:00:00 0.628312
123464 2009-01-03 00:00:00 0.83623
123465 2009-01-04 00:00:00 0.516042
123466 2009-01-04 00:00:00 0.657275
123467 2009-01-04 00:00:00 0.73612
123468 2009-01-04 00:00:00 0.714538
123469 2009-01-04 00:00:00 0.291894
123470 2009-01-05 00:00:00 0.735031
123471 2009-01-05 00:00:00 0.0947181
123472 2009-01-05 00:00:00 0.551639
123473 2009-01-05 00:00:00 0.689682
123474 2009-01-06 00:00:00 0.526536
123475 2009-01-06 00:00:00 0.541074


fno fid fdate fother
20090102001 123457 2009-01-02 00:00:00 0.669186
20090102002 123458 2009-01-02 00:00:00 0.770396
20090102003 123459 2009-01-02 00:00:00 0.659152
20090102004 123460 2009-01-02 00:00:00 0.129873
20090103001 123461 2009-01-03 00:00:00 0.0639277
20090103002 123462 2009-01-03 00:00:00 0.835485
20090103003 123463 2009-01-03 00:00:00 0.628312
20090103004 123464 2009-01-03 00:00:00 0.83623
20090104001 123465 2009-01-04 00:00:00 0.516042
20090104002 123466 2009-01-04 00:00:00 0.657275
20090104003 123467 2009-01-04 00:00:00 0.73612
20090104004 123468 2009-01-04 00:00:00 0.714538
20090104005 123469 2009-01-04 00:00:00 0.291894
20090105001 123470 2009-01-05 00:00:00 0.735031
20090105002 123471 2009-01-05 00:00:00 0.0947181
20090105003 123472 2009-01-05 00:00:00 0.551639
20090105004 123473 2009-01-05 00:00:00 0.689682
20090106001 123474 2009-01-06 00:00:00 0.526536
20090106002 123475 2009-01-06 00:00:00 0.541074