求一sql 计算

[wwfgu00ing]

表
prcode

tl000001
tl000002


在下次插入的时候插入tl000003 依次类推

注意
1.没有数据的情况，默认是tl000001
前面以tl开头 后面以六位数字结尾，不足，左面补0

[叶子]

declare @prcode table (colname varchar(8))

declare @i int ;set @i=1
while @i<11
begin
	insert into @prcode 
	select 'tl'+right(replicate('0',6)+ltrim(@i),6)
	set @i=@i+1
end

select * from @prcode
/*
colname
--------
tl000001
tl000002
tl000003
tl000004
tl000005
tl000006
tl000007
tl000008
tl000009
*/

[AcHerat]

create function get_procode()
returns varchar(8)
as
declare @code varchar(8)
declare @i int
select @i = max(convert(int,right(procode,6))) from tb
set @code = 't1' + right(1000000+@i+1,6)
return @code
go

insert into tb
select dbo.get_procode()

[AcHerat]

create table tb(procode varchar(8))
insert into tb
select 't1000001'
go

create function get_procode()
returns varchar(8)
as
begin
declare @code varchar(8)
declare @i int
select @i = max(convert(int,right(procode,6))) from tb
set @code = 't1' + right(1000000+@i+1,6)
return @code
end
go

insert into tb
select dbo.get_procode()

insert into tb
select dbo.get_procode()

select *
from tb


drop function get_procode
drop table tb

/************

procode
--------
t1000001
t1000002
t1000003

(3 行受影响)

[-晴天]

create table prcode(id varchar(10),col int)
go
--如果只考虑单条记录插入:
create trigger getid
on prcode
INSTEAD OF insert
as
begin
insert into prcode select 'tl'+right('0000000'+ltrim((select isnull(convert(int,right(max(id),6)),0) from prcode)+1),6),col from inserted
end
go
insert into prcode(col) select 25
insert into prcode(col) select 31
select * from prcode
/*
id         col
---------- -----------
tl000001   25
tl000002   31

(2 行受影响)
*/
go
drop table prcode

[-晴天]

create table prcode(id varchar(10),col int)
go
--如果考虑多条记录一次插入:
create trigger getid
on prcode
INSTEAD OF insert
as
begin
declare @i int
select @i=isnull(convert(int,right(max(id),6)),0) from prcode
insert into prcode select 'tl'+right('0000000'+ltrim(@i+rn),6),col from(
  select row_number()over(order by newid())rn,col from inserted
)t
end
go
insert into prcode(col) select 25 union all select 32 union all select 58
select * from prcode
/*
id         col
---------- -----------
tl000001   58
tl000002   25
tl000003   32

(3 行受影响)

*/
go
drop table prcode

[cd731107]

create table tb(procode varchar(8))

select procode=isnull('t1'+right('00000'+ltrim(cast(right(max(procode),6) as int)+1),6),'t1000001') from tb

select * from tb

/*               
-------------- 
t1000001

（所影响的行数为 1 行）
*/

insert tb select 't1000001'

select * from tb

/*               
-------------- 
t1000002

（所影响的行数为 1 行）
*/

[cd731107]

create table tb(procode varchar(8))

select procode=isnull('t1'+right('00000'+ltrim(cast(right(max(procode),6) as int)+1),6),'t1000001') from tb


/*               
-------------- 
t1000001

（所影响的行数为 1 行）
*/

insert tb select 't1000001'

select procode=isnull('t1'+right('00000'+ltrim(cast(right(max(procode),6) as int)+1),6),'t1000001') from tb


/*               
-------------- 
t1000002

（所影响的行数为 1 行）
*/