--修改和测试create table test (tID varchar(8) PRIMARY Key) ON [PRIMARY] insert test select 'Temp1' union all select 'Temp2' union all select '1' union all select '2' union all select '3' union all select '4'update a set tID = (select max(tID) from test where substring(tID,1,4)<>'Temp')+(select count(*) from test where substring(tID,1,4)='Temp' and tID<=a.tID) from test a where substring(a.tID,1,4)='Temp'select * from test--结果 tID -------- 1 2 3 4 5 6(所影响的行数为 6 行)
另类方法:create table test (tID varchar(8) PRIMARY Key) ON [PRIMARY] insert test select 'Temp1' union all select 'Temp2' union all select '1' union all select '2' union all select '3' union all select '4'declare @tID int select @tID=max(tID) from test where substring(tID,1,4)<>'Temp'update test set @tID=@tID+1, tID = @tID where substring(tID,1,4)='Temp'select * from test
奥 这种可以 ,呵呵 但是字段长的怎么解决如下面 学生表 两个字段(sID sClassID ) sClassID 为班级表,sID 是根据所在班级+最大序列号 我定义了一个函数getMaxID 其中参数为sClassID , 这种问题怎么处理?? create table student (sID varchar(50) PRIMARY Key,sClassID varchar(50)) ON [PRIMARY]insert student select 'Temp3704000120051001032812','370401' union all select 'Temp0037098220050102121519','370402' union all select 'Temp0037098220050102121209','370402' union all select 'Temp0037098220050102121409','370402' union all select '370401000043' ,'370401' union all select '370402000045','370402' union all select '370402000046' ,'370402' union all select '370402000047','370402' CREATE FUNCTION getMaxID (@sClassID varchar(10)) RETURNS varchar(20) AS BEGIN declare @maxID int--获得最大ID号 并且 不足六位 declare @sID varchar(20) Select @maxID = isnull(max(substring(sID,7,6)),0)+1 from student where substring(sID,1,6)=substring(@sClassID,1,6)
set @sID=substring(@sClassID,1,6) + right('000000'+convert(varchar(6),@maxID),6) --格式化左边补领RETURN @sID END update student set sID = dbo.getMaxID(sClassID) where substring(sID,1,4)='Temp'
insert test
select 'Temp1' union all
select 'Temp2' union all
select '1' union all
select '2' union all
select '3' union all
select '4'update a
set tID = (select max(tID) from test where substring(tID,1,4)<>'Temp')+(select count(*) from test where substring(tID,1,4)='Temp' and tID<=a.tID)
from test a
where substring(a.tID,1,4)='Temp'select * from test--结果
tID
--------
1
2
3
4
5
6(所影响的行数为 6 行)
insert test
select 'Temp1' union all
select 'Temp2' union all
select '1' union all
select '2' union all
select '3' union all
select '4'declare @tID int
select @tID=max(tID) from test where substring(tID,1,4)<>'Temp'update test
set @tID=@tID+1,
tID = @tID
where substring(tID,1,4)='Temp'select * from test
但是字段长的怎么解决如下面 学生表 两个字段(sID sClassID )
sClassID 为班级表,sID 是根据所在班级+最大序列号
我定义了一个函数getMaxID 其中参数为sClassID ,
这种问题怎么处理??
create table student (sID varchar(50) PRIMARY Key,sClassID varchar(50)) ON [PRIMARY]insert student
select 'Temp3704000120051001032812','370401' union all
select 'Temp0037098220050102121519','370402' union all
select 'Temp0037098220050102121209','370402' union all
select 'Temp0037098220050102121409','370402' union all
select '370401000043' ,'370401' union all
select '370402000045','370402' union all
select '370402000046' ,'370402' union all
select '370402000047','370402'
CREATE FUNCTION getMaxID (@sClassID varchar(10))
RETURNS varchar(20) AS
BEGIN
declare @maxID int--获得最大ID号 并且 不足六位
declare @sID varchar(20)
Select @maxID = isnull(max(substring(sID,7,6)),0)+1 from student
where substring(sID,1,6)=substring(@sClassID,1,6)
set @sID=substring(@sClassID,1,6) + right('000000'+convert(varchar(6),@maxID),6) --格式化左边补领RETURN @sID
END
update student set sID = dbo.getMaxID(sClassID) where substring(sID,1,4)='Temp'