求一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)
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())
[解决办法]
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
试试这个
[解决办法]
这有一个列子,给楼主参照
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx
