如何更新某列使之成为序列

/*id    sn  name 

GUID      小黑 

GUID      小白 

GUID      小花 

.... 



是一个班级的学生表。sn内容为空，如何更新sn列，并使之成为01,02等序号，并且按name排序 

id    sn  name 

GUID  01    小白 

GUID  02    小黑 

GUID  03    小花  */



use tempdb

go

if object_id('tempdb..#') is not null

drop table #



create table #(id varchar(20)

				,sn varchar(20) 

				,name varchar(20))



insert into #

select 'GUID',null,'小白'

union all

select 'GUID',null,'小黑'

union all

select 'GUID',null,'小花'





select * from #



select id 

	,'0'+cast(row_number() over(partition by id order by id) as varchar(10)) as sn

	,name

from #

order by name

declare @tb table (sn varchar(10),name varchar(10))

insert into @tb select '','小黑'

insert into @tb select '','小白'

insert into @tb select '','小花'



select count(1) from @tb



update tp set sn=right('00'+ltrim((select count(1) from @tb where name<=tp.name)),3)from @tb tp



select * from @tb order by name

--2005

CREATE TABLE tb(id VARCHAR(20),sn VARCHAR(10),name VARCHAR(10))

GO

INSERT tb SELECT 'GUID',NULL  ,    '小黑' 

UNION ALL SELECT 'GUID',NULL ,     '小白' 

UNION ALL SELECT 'GUID',NULL,      '小花'



update a set sn=b.snum from tb a,(select id,name, snum=right('00'+ltrim(row_number() over (Partition by id order by name)),2) from tb ) b

where a.id=b.id and a.name=b.name



select * from tb

order by sn



id                   sn         name

-------------------- ---------- ----------

GUID                 01         小白

GUID                 02         小黑

GUID                 03         小花



(3 行受影响)

CREATE TABLE tb(id VARCHAR(20),sn VARCHAR(10),name VARCHAR(10))

GO

INSERT tb SELECT 'GUID',NULL  ,    '小黑' 

UNION ALL SELECT 'GUID',NULL ,     '小白' 

UNION ALL SELECT 'GUID',NULL,      '小花'

GO



UPDATE a SET sn=RIGHT('00' + RTRIM((SELECT COUNT(*) FROM tb WHERE name < = a.name)),2) FROM tb a

SELECT * FROM tb ORDER BY name



GO



DROP TABLE tb

GO 

declare @tb table (sn varchar(10),name varchar(10))

insert into @tb select '','小黑'

insert into @tb select '','小白'

insert into @tb select '','小花'



update tp set sn=right('000'+ltrim((select count(1) from @tb where name<=tp.name)),3)from @tb tp



select * from @tb order by name

update a

   set sn=right('00'+cast((select count(1) from tab where name<=a.name) as varchar),2)

from tab a

update 表 t set sn=right('0'+rtrim((select count(*) from 表 where name<=t.name)),2)