求一SQL语句自增ID,急!!!分不够再加

[caibaoying]

比如 订货单号 我要自增
比如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
帮帮忙```分不够我加

[中国风]

这有一个列子,给楼主参照
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx

[leo_lesley]

例子：

select right('000'+'12',3)

[caixia615]

关注

[leo_lesley]

"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 "

[tmc1703]

换算成数字比较好弄,可以把日期2007-01-08转成20070108然后加自增量
比如:20070108x1000+n (n为自增量) 那么结果再处理就比较好弄了

[xiequanqin]

"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 "

[leo_lesley]

--例子

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

[mengmou]

--试试
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 行）

[leo_lesley]

--例子

select replace(convert(char(10),getdate(),21),'-','')+right('000'+cast(ISNULL(max(cast(right('070101014',3) as int)+1),'001') as varchar(3)),3)

[bugchen888]

--测试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

[jacobsan]

"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 "

--如上

[marco08]

create table T(No varchar(10))
go
create function fun(@curDate datetime)
returns varchar(10)
as
begin
declare @No varchar(10)
select @No=max(No) from T where No like right(convert(char(8), @curDate, 112), 6)+'%'
if @No is null
set @No=right(convert(char(8), @curDate, 112), 6)+'001'
else
set @No=left(@No, 6)+ right('000'+rtrim(right(@No, 3)+1), 3)

return @No
end

insert T select dbo.fun(getdate())

[yangjianw2004]

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

试试这个