你的name是主键吗? 是的话就好说了。try: select [name],course,right('0000'+cast((select sum(1) from 你的表 where [name] <= a.[name]) as varchar),4) as 序号 from 你的表 a
--就是这样写: select *,序号=identity(int,1,1) into #t from test where course >90 select name,course,序号=right(10000+序号,4) from #t drop table #t
select a.* ,identity(int,1,1) into # from table a order by course select * from #
select *,identity(1,1) 序號 into #tmp from table select * from #tmp 如果有唯一标志字段就好了
table4: name course a 100 b 92 c 23 d 50 sql语句: select *,idt=identity(int,1,1) into temp from table4 where course >90 select name,score,'000'+cast(idt as varchar(4)) as id from temp drop table temp -----------测试------------------------------------ name course id a 100 0001 b 92 0002 --------------------------------------------------- (所影响的行数为 2 行) (所影响的行数为 2 行)
select [name],course,right('0000'+cast((select sum(1) from 你的表 where [name] <= a.[name]) as varchar),4) as 序号
from 你的表 a
select *,序号=identity(int,1,1) into #t from test where course >90
select name,course,序号=right(10000+序号,4) from #t
drop table #t
select * from #
select * from #tmp
如果有唯一标志字段就好了
name course
a 100
b 92
c 23
d 50
sql语句:
select *,idt=identity(int,1,1) into temp from table4 where course >90
select name,score,'000'+cast(idt as varchar(4)) as id from temp
drop table temp
-----------测试------------------------------------
name course id
a 100 0001
b 92 0002
---------------------------------------------------
(所影响的行数为 2 行)
(所影响的行数为 2 行)