mssql 学校学生排号

[liaoyukun111]

假设一个学校有6个年级，各有6个班，
现在要求是：每个班级都有自己的号，都从01开始，所以各班第一个号是，0101,0201,0301,0401,0501,0601,
不用考虑班多人多的问题。
写一SQL语句完成一个年级的排号

[Rotel-刘志东]

select row_number() partion by(order by number)as '排号' from tb

[Rotel-刘志东]

---上边写错了
select row_number() over(order by number)as '排号' from tb

[幸运的意外]

如果楼主是2005版本以上：
select row_number() over(partion class order by number) as '班内序号' from tb

[快溜]

你都没说表结构，sql2005用row_number,2000就用临时表SQL Server2005杂谈（3）：四个排名函数（row_number、rank、dense_rank和ntile）

[AcHerat]

--sql2005

select stu_id,class_id,ltrim(class_id) + right('00000' + ltrim(row_number() over (partition by class_id order by getdate())),2) as [学号]
from tb

[--小F--]

select
 stu_id,class_id,ltrim(class_id) + right('00' + ltrim(row_number() over (partition by class_id order by getdate())),2) as [学号]
from
 tb