如下表是学生基本信息数据,现在要生成一个流水号作为学号格式如下,例如2018年入学的第一位学生的学号 20180001
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5yNwIjM5gTO2cjY3IGNjRDZyYzX5UTNyATM4EzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
declare @prefix varchar(10)
select @prefix=cast(year(getdate()) as varchar(50))
select @prefix+right('0000'+cast(ID as varchar(10)),4) as num,ID,name,age
from TableA
效果如下图
还有一种方法是利用substring 和len函数
select @prefix+ substring('0000',1,4-len(ID))+ID as num1,ID,name,age
from TableA
利用replicate函数和len或者DATALENGTH函数
declare @prefix varchar(10)='2018'
select @prefix+ replicate('0',3-len(ID))+cast(ID as varchar(50))
from TableA