求一SQL语句自增ID,急!!!分不够再加
比如   订货单号   我要自增
比如2007-1-1
那么ID为070101***
其中***为自增
比如第一个为
001
第二个为002
依次类推
"select   concat(   substring(   year(current_date()),-2),   month(current_date()),   day(current_date()),   IFNULL(max(substring(oi.sn,-3)+1), '001 ')   )   "
+   "   from   OrderInfo   oi   "
我这样写的时候
比如
070101014
下一个它就07010115
我要的是070101015
少了个0
帮帮忙```分不够我加


------解决方案--------------------
"select concat( substring( year(current_date()),-2), month(current_date()), day(current_date()), right( '000 '+cast(ISNULL(max(cast(right( '070101014 ',3) as int)+1), '001 ') as varchar(3)),3) ) "
+ " from OrderInfo oi "
------解决方案--------------------
换算成数字比较好弄,可以把日期2007-01-08转成20070108然后加自增量
比如:20070108x1000+n (n为自增量) 那么结果再处理就比较好弄了
------解决方案--------------------

"select concat( substring( year(current_date()),-2), month(current_date()), day(current_date())),LEFT(3-LEN(RTRIM(ISNULL(max(substring(oi.sn,-3)+1),1))))+RTRIM(ISNULL(max(substring(oi.sn,-3)+1),1)) "
+ " from OrderInfo oi "
------解决方案--------------------
--试试
create table #date(date datetime,id varchar(20))
insert into #date(date)
select '20070101 ' union all
select '20070101 ' union all
select '20070101 ' union all
select '20070102 ' union all
select '20070102 '

declare @date datetime,@id int,@sid varchar(20)
update #date
set @id = case when date = @date then @id + 1 else 1 end ,@date = date
,id = '0 ' + convert(char(8),right(convert(char(8),date,112),6)*1000 + @id)

select * from #date

drop table #date

date id
------------------------------------------------------ --------------------
2007-01-01 00:00:00.000 070101001
2007-01-01 00:00:00.000 070101002
2007-01-01 00:00:00.000 070101003
2007-01-02 00:00:00.000 070102001
2007-01-02 00:00:00.000 070102002

（所影响的行数为 5 行）
------解决方案--------------------
--测试Table
CREATE TABLE dbo.#
(sn NVARCHAR(20) )

--执行一次
INSERT INTO #
SELECT CONVERT(NVARCHAR(20),getDate(),12)+replace(str(ISNULL(MAX(Right(sn,3)),0)+1,3),Space(1), '0 ')
FROM #
SELECT * FROM #


(1 row(s) affected)

sn
--------------------
070108001

--执行第二次
(1 row(s) affected)

sn
--------------------
070108001
070108002
------解决方案--------------------
"select concat( substring( year(current_date()),-2), month(current_date()), day(current_date()), IFNULL((right(cast(max(substring(oi.sn,-3)+100001 as varchar(100)),3), '001 ') ) "
+ " from OrderInfo oi "

--如上

------解决方案--------------------
create table T(No varchar(10))
go
create function fun(@curDate datetime)
returns varchar(10)