1,查询中增加行号
select row_number()over(order by (select 1)) as ID,* from FourDD
2,创建一个函数,转换整数为格式化的字符串,左边添加0
2.1 创建函数
Create function fsPadLeft1(@var varchar(200),@padChar char(1)='0',@len int=4)
returns varchar(1000)
as
Begin
return replicate(@PadChar,@len-Len(@var))+@var
end
2.2 调用过程
select dbo.fsPadLeft(row_number()over(order by (select 1)),'0',4) as ID,* from FourDD
3,利用临时表增加行号
以下例子中 #temptable是临时表,先创建一个临时表,然后列表
use [TOTO]
select fdno=Identity(int,0,1),* into #temptable from FourDD order by DrawDate Select top 10
DrawDate,frst,snd,thrd,star,cons,
"no" = CASE
WHEN fdno < 10 THEN '000'+CAST(fdno as varchar(10))
WHEN fdno < 100 THEN '00'+CAST(fdno as varchar(10))
WHEN fdno < 1000 THEN '0'+CAST(fdno as varchar(10))
ELSE '0'+CAST(fdno as varchar(10))
END
From #temptable
Drop Table #temptable
4, 表变量的运用
declare @TableVar table (
ID int identity(1,1),
DrawnDate date NOT NULL,
FirstPrize nchar(50) NOT NULL )
Insert Into @TableVar (DrawnDate, FirstPrize)
Select DrawDate, star
From FourDD
Select Top 10 FirstPrize
from @TableVar