sql查询时加上行号

sql查询时加上行号

 

sql查询的时候有时候要加上行号，这里做个备忘~

1(建议采用),
select row_number() over (order by col1) as no,newid() as id,*
from Table1
order by col1

2,
select no=identity(int,1,1),newid() as id,* 
into  #temptable From  Table1
order by col1
Select * From #temptable
Drop Table #temptable

3,
SELECT (SELECT sum(1)  FROM Table1
 WHERE col1 <= a.col1) AS no, *  
 FROM Table1 a