1.
select [num]=identity(int,1,1),* into # from 学生表select [学号]='Y05'+right(YXDM,2)+(select count(1) from # where YXDM=t.YXDM and num<=t.num),
*
from # t
select [num]=identity(int,1,1),* into # from 学生表select [学号]='Y05'+right(YXDM,2)+(select count(1) from # where YXDM=t.YXDM and num<=t.num),
*
from # t
b.*,
'Y05'+right(b.YXDM,2)+'000'+rtrim(b.CNT)
from
(select
a.*,
CNT = (select count(*) from #T where YXDM=a.YXDM and ID<=a.ID)
from
#T a) b
(@YXDM char(6)) --入口参数:院系代码
as
begin
declare @XSDM char(12)
select @XSDM=max(XSDM)
from 表
where YXDM=@YXDM
select @XSDM=left(@XSDM,9)+right(1000+cast(right(@XSDM,3) as smallint)+1,3)
return(@XSDM)
end
Go
YXDM ZYDM
001 010101
001 010201
001 012101
002 010202
002 020702
003 020205
当然,以前还存在着唯一的一个考号(但太长了,所以要另外建一个学号)现在该表中没有学号,要求还要建立一个学号字段,字符型的,长度为8位,以上面的数据例,那么学号应该仿效为:
Y0501001
Y0501002
Y0501003
Y0502001
Y0502002
Y0503001
select YXSM,ZYDM,NOO='Y'+'05'+RIGHT(YXSM,2)+right(1000
+(select count(*) from NLQ where YXSM=a.YXSM AND ZYDM<=A.ZYDM AND KSBH<=A.KSBH)
,3)
from NLQ A查询结果如下:
001 010105 Y0501001
001 010105 Y0501002
001 010105 Y0501003
001 010105 Y0501004
001 010105 Y0501005
001 010105 Y0501006
001 010105 Y0501007
001 010105 Y0501008
001 010105 Y0501009
001 020101 Y0501002
001 020101 Y0501003
001 020202 Y0501002
001 020202 Y0501003
001 020202 Y0501004
001 020202 Y0501005
001 020202 Y0501006