一个sql语句
如何用一个SQL语句产生一系列流水编号，格式如下：
20070327-0001
20070327-0002
20070327-0003
20070327-0004
20070327-0005
......
20070328-0001
20070328-0002
......
Thanks!
[解决办法]
select replace(convert(char(10),getdate(),120), '- ', ' ')+ '- '+right( '0000 '+ cast(isnull(max(cast(right(id,4) as int)),0)+1 as varchar),4) from tb where charindex(replace(convert(char(10),getdate(),120), '- ', ' ')+ '- ',id)> 0
[解决办法]
WHILE 循環
[解决办法]
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-00012008-10-27-00022008-10-27-00032008-10-27-00042008-10-27-00052008-10-27-00062008-10-27-00072008-10-27-00082008-10-27-00092008-10-27-00102008-10-27-00112008-10-27-00122008-10-27-00132008-10-27-00142008-10-27-00152008-10-27-00162008-10-27-00172008-10-27-00182008-10-27-00192008-10-27-00202008-10-27-00212008-10-27-00222008-10-27-00232008-10-27-00242008-10-27-00252008-10-27-00262008-10-27-00272008-10-27-00282008-10-27-00292008-10-27-00302008-10-27-00312008-10-27-00322008-10-27-00332008-10-27-00342008-10-27-00352008-10-27-00362008-10-27-00372008-10-27-00382008-10-27-00392008-10-27-00402008-10-27-00412008-10-27-00422008-10-27-00432008-10-27-00442008-10-27-00452008-10-27-00462008-10-27-00472008-10-27-00482008-10-27-00492008-10-27-00502008-10-27-00512008-10-27-00522008-10-27-00532008-10-27-00542008-10-27-00552008-10-27-00562008-10-27-00572008-10-27-00582008-10-27-00592008-10-27-00602008-10-27-00612008-10-27-00622008-10-27-00632008-10-27-00642008-10-27-00652008-10-27-00662008-10-27-00672008-10-27-00682008-10-27-00692008-10-27-00702008-10-27-00712008-10-27-00722008-10-27-00732008-10-27-00742008-10-27-00752008-10-27-00762008-10-27-00772008-10-27-00782008-10-27-00792008-10-27-00802008-10-27-00812008-10-27-00822008-10-27-00832008-10-27-00842008-10-27-00852008-10-27-00862008-10-27-00872008-10-27-00882008-10-27-00892008-10-27-00902008-10-27-00912008-10-27-00922008-10-27-00932008-10-27-00942008-10-27-00952008-10-27-00962008-10-27-00972008-10-27-00982008-10-27-00992008-10-27-01002008-10-27-01012008-10-27-01022008-10-27-01032008-10-27-01042008-10-27-01052008-10-27-01062008-10-27-01072008-10-27-01082008-10-27-01092008-10-27-01102008-10-27-01112008-10-27-01122008-10-27-01132008-10-27-01142008-10-27-01152008-10-27-01162008-10-27-01172008-10-27-01182008-10-27-01192008-10-27-01202008-10-27-01212008-10-27-01222008-10-27-01232008-10-27-01242008-10-27-01252008-10-27-01262008-10-27-01272008-10-27-01282008-10-27-01292008-10-27-01302008-10-27-01312008-10-27-01322008-10-27-01332008-10-27-01342008-10-27-01352008-10-27-01362008-10-27-01372008-10-27-01382008-10-27-01392008-10-27-01402008-10-27-01412008-10-27-01422008-10-27-01432008-10-27-01442008-10-27-01452008-10-27-01462008-10-27-01472008-10-27-01482008-10-27-01492008-10-27-01502008-10-27-01512008-10-27-01522008-10-27-01532008-10-27-01542008-10-27-01552008-10-27-01562008-10-27-01572008-10-27-01582008-10-27-01592008-10-27-01602008-10-27-01612008-10-27-01622008-10-27-01632008-10-27-01642008-10-27-01652008-10-27-01662008-10-27-01672008-10-27-01682008-10-27-01692008-10-27-01702008-10-27-01712008-10-27-01722008-10-27-01732008-10-27-01742008-10-27-01752008-10-27-01762008-10-27-01772008-10-27-01782008-10-27-01792008-10-27-01802008-10-27-01812008-10-27-01822008-10-27-01832008-10-27-01842008-10-27-01852008-10-27-01862008-10-27-01872008-10-27-01882008-10-27-01892008-10-27-01902008-10-27-01912008-10-27-01922008-10-27-01932008-10-27-01942008-10-27-01952008-10-27-01962008-10-27-01972008-10-27-01982008-10-27-01992008-10-27-02002008-10-27-0201....*/
[解决办法]
declare @tb table( [value] nvarchar(50))declare @date nchar(9) declare @n intset @date = convert(char(8),getdate(),112) + '-'set @n = 1while @n < 10begin insert into @tb values(@date + right('0000'+ cast(@n as nvarchar),4)) set @n = @n + 1endselect * from @tb20081027-000120081027-000220081027-000320081027-000420081027-000520081027-000620081027-000720081027-000820081027-0009
[解决办法]
DECLARE @NUM INTSET @NUM=1CREATE TABLE #A (S_DATE VARCHAR(20),S_NO VARCHAR(20))WHILE @NUM<10BEGININSERT INTO #A (S_DATE,S_NO)SELECT CONVERT(VARCHAR(10),GETDATE(),112), (CONVERT(VARCHAR(10),GETDATE(),112) +'-'+REPLICATE(0,4-LEN(COUNT(*)+1))+CAST((COUNT(*)+1) AS VARCHAR))FROM #ASET @NUM=@NUM+1ENDSELECT * FROM #ADROP TABLE #A/**20081027 20081027-000120081027 20081027-000220081027 20081027-000320081027 20081027-000420081027 20081027-000520081027 20081027-000620081027 20081027-000720081027 20081027-000820081027 20081027-0009