数据表更新

Question

学号只是一个示意，主要用于批量输入有这种规律的数据

Answer 1

create table stunotest
(
id int,
name varchar(20),
class varchar(20),
type varchar(20),
sex varchar(20),
birthday datetime,
stu_no varchar(20)
)
goinsert into stunotest values(1,'a1','b1','t1','男','1994-5-1',null)
insert into stunotest values(2,'a2','b1','t2','女','1995-8-1',null)
insert into stunotest values(3,'a3','b2','t1','女','1994-6-3',null)
insert into stunotest values(4,'a4','b1','t2','男','1995-8-1',null)
insert into stunotest values(5,'a5','b2','t1','女','1994-6-3',null)create proc sp_update
as
select identity(int,1,1) newid,a.id into #tmp from (select top 100 PERCENT  * from stunotest order by class,type,sex) a
update stunotest set stu_no= '2004'+right('0000'+cast(b.newid as varchar),4) 
from
stunotest a ,#tmp b where
a.id=b.id
go
 
sp_updateselect * from stunotest-------------------------------------------------
1 a1 b1 t1 男 1994-05-01 00:00:00.000 20040001
2 a2 b1 t2 女 1995-08-01 00:00:00.000 20040003
3 a3 b2 t1 女 1994-06-03 00:00:00.000 20040004
4 a4 b1 t2 男 1995-08-01 00:00:00.000 20040002
5 a5 b2 t1 女 1994-06-03 00:00:00.000 20040005
------------------------------------------------