34,838
社区成员




/*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)