以下代码一样,只是测试数据不同.--没有数据的时候. create table tb(col varchar(20)) gocreate proc my_proc as begin declare @col as varchar(20) if not exists(select 1 from tb) select '0001' else begin select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m, (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1 if @col is not null select @col else begin select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb select @col end end end goexec my_procdrop table tb drop proc my_proc/*
---- 0001(所影响的行数为 1 行) */--存在有断号的时候. create table tb(col varchar(20)) insert into tb values('2009JXX0001') insert into tb values('2009CXX0003') gocreate proc my_proc as begin declare @col as varchar(20) if not exists(select 1 from tb) select '0001' else begin select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m, (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1 if @col is not null select @col else begin select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb select @col end end end goexec my_procdrop table tb drop proc my_proc/*
---- 0002(所影响的行数为 1 行) */--不存在断号时. create table tb(col varchar(20)) insert into tb values('2009JXX0001') insert into tb values('2009CXX0002') gocreate proc my_proc as begin declare @col as varchar(20) if not exists(select 1 from tb) select '0001' else begin select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m, (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1 if @col is not null select @col else begin select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb select @col end end end goexec my_procdrop table tb drop proc my_proc/*
---- 0003(所影响的行数为 1 行) */
你自己再把条件加上即可. 例如: create table tb(col varchar(20)) gocreate proc my_proc @year varchar(4) as begin declare @col as varchar(20) if not exists(select 1 from tb where left(col,4) = @year) select '0001' else begin select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from (select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) m, (select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) n where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1 if @col is not null select @col else begin select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb where left(col,4) = @year select @col end end end goexec my_proc '2010'drop table tb drop proc my_proc/* ---- 0001(所影响的行数为 1 行) */
create table tb(col varchar(20))
gocreate proc my_proc
as
begin
declare @col as varchar(20)
if not exists(select 1 from tb)
select '0001'
else
begin
select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
if @col is not null
select @col
else
begin
select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
select @col
end
end
end
goexec my_procdrop table tb
drop proc my_proc/*
----
0001(所影响的行数为 1 行)
*/--存在有断号的时候.
create table tb(col varchar(20))
insert into tb values('2009JXX0001')
insert into tb values('2009CXX0003')
gocreate proc my_proc
as
begin
declare @col as varchar(20)
if not exists(select 1 from tb)
select '0001'
else
begin
select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
if @col is not null
select @col
else
begin
select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
select @col
end
end
end
goexec my_procdrop table tb
drop proc my_proc/*
----
0002(所影响的行数为 1 行)
*/--不存在断号时.
create table tb(col varchar(20))
insert into tb values('2009JXX0001')
insert into tb values('2009CXX0002')
gocreate proc my_proc
as
begin
declare @col as varchar(20)
if not exists(select 1 from tb)
select '0001'
else
begin
select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
if @col is not null
select @col
else
begin
select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
select @col
end
end
end
goexec my_procdrop table tb
drop proc my_proc/*
----
0003(所影响的行数为 1 行)
*/
例如:
create table tb(col varchar(20))
gocreate proc my_proc @year varchar(4)
as
begin
declare @col as varchar(20)
if not exists(select 1 from tb where left(col,4) = @year)
select '0001'
else
begin
select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
(select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) m,
(select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) n
where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
if @col is not null
select @col
else
begin
select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb where left(col,4) = @year
select @col
end
end
end
goexec my_proc '2010'drop table tb
drop proc my_proc/*
----
0001(所影响的行数为 1 行)
*/