有表如下
id sn name
GUID 小黑
GUID 小白
GUID 小花
....是一个班级的学生表。sn内容为空,如何更新sn列,并使之成为01,02等序号,并且按name排序
id sn name
GUID 01 小白
GUID 02 小黑
GUID 03 小花
....
id sn name
GUID 小黑
GUID 小白
GUID 小花
....是一个班级的学生表。sn内容为空,如何更新sn列,并使之成为01,02等序号,并且按name排序
id sn name
GUID 01 小白
GUID 02 小黑
GUID 03 小花
....
set sn=right('00'+cast((select count(1) from tab where name<=a.name) as varchar),2)
from tab a
set sn = ( select count(*) from table where name <= t.name )
from table t
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 tpselect * from @tb order by namesn name
001 小白
002 小黑
003 小花
GO
INSERT tb SELECT 'GUID',NULL , '小黑'
UNION ALL SELECT 'GUID',NULL , '小白'
UNION ALL SELECT 'GUID',NULL, '小花'
GOUPDATE a SET sn=RIGHT('00' + RTRIM((SELECT COUNT(*) FROM tb WHERE name < = a.name)),2) FROM tb a
SELECT * FROM tb ORDER BY nameGODROP TABLE tb
GO
--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.nameselect * from tb
order by snid sn name
-------------------- ---------- ----------
GUID 01 小白
GUID 02 小黑
GUID 03 小花(3 行受影响)
insert into @tb select '','小黑'
insert into @tb select '','小白'
insert into @tb select '','小花'select count(1) from @tbupdate tp set sn=right('00'+ltrim((select count(1) from @tb where name<=tp.name)),3)from @tb tpselect * from @tb order by name
学习
Select @i = 1
update tab
Set tab.i = @i , @i = @i + 1
/*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