如何用一个SQL语句产生一系列流水编号

来源：互联网发布：5.11淘宝真假编辑：程序博客网时间：2024/04/30 12:03

Q:如何用一个SQL语句产生一系列流水编号，格式如下：
20070327-0001
20070327-0002
20070327-0003
20070327-0004
20070327-0005
......
20070328-0001
20070328-0002
......

select replace(convert(char(10),getdate(),120), '- ', ' ')+'-'+right(10000+ROW_NUMBER() over(order by a.id),4)
from sysobjects a,sysobjects b
/*
------------------------------------------------------------------------------------------
2008-10-27-0001
2008-10-27-0002
2008-10-27-0003
2008-10-27-0004
2008-10-27-0005
2008-10-27-0006
2008-10-27-0007
2008-10-27-0008
2008-10-27-0009
2008-10-27-0010
2008-10-27-0011
2008-10-27-0012
2008-10-27-0013
2008-10-27-0014
2008-10-27-0015
2008-10-27-0016
2008-10-27-0017
2008-10-27-0018
2008-10-27-0019
2008-10-27-0020
2008-10-27-0021
2008-10-27-0022
2008-10-27-0023

....
*/

A2:

declare @tb table
(
    [value] nvarchar(50)
)

declare @date nchar(9)
declare @n int
set @date = convert(char(8),getdate(),112) + '-'
set @n = 1

while @n < 10
begin
    insert into @tb values(@date + right('0000'+ cast(@n as nvarchar),4))
    set @n = @n + 1
end

select * from @tb

20081027-0001
20081027-0002
20081027-0003
20081027-0004
20081027-0005
20081027-0006
20081027-0007
20081027-0008
20081027-0009

A3:

select replace(convert(char(10),getdate(),120), '-', '')+'-'+right(10000+ROW_NUMBER() over(order by a.id),4)
from sysobjects a,sysobjects b

20081027-0001
20081027-0002
20081027-0003
20081027-0004
20081027-0005
20081027-0006
20081027-0007
20081027-0008
20081027-0009
、、、、、、、