SQL SERVER2005 RowNumber() 函数
USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader
GO
功能：在返回的记录集中新增加一计数列，
ROW_NUMBER() OVER (order by OrderDate)as RowNumber 以OrderDate的从小到大的顺序(顺序可以改)排
序
RowNumber 按照排序结果顺序从1开始记数编号
结果如下:
SalesOrderID OrderDate RowNumber
--------- ---------------- ---------
43659 2001-07-01 00:00:00.000 1
43660 2001-07-01 00:00:00.000 2
43661 2001-07-01 00:00:00.000 3
43662 2001-07-01 00:00:00.000 4
43663 2001-07-01 00:00:00.000 5
43664 2001-07-01 00:00:00.000 6
43665 2001-07-01 00:00:00.000 7
43666 2001-07-01 00:00:00.000 8
43667 2001-07-01 00:00:00.000 9
43668 2001-07-01 00:00:00.000 10
43669 2001-07-01 00:00:00.000 11
43670 2001-07-01 00:00:00.000 12
43671 2001-07-01 00:00:00.000 13
43672 2001-07-01 00:00:00.000 14
43673 2001-07-01 00:00:00.000 15
43674 2001-07-01 00:00:00.000 16
43675 2001-07-01 00:00:00.000 17
43676 2001-07-01 00:00:00.000 18
43677 2001-07-01 00:00:00.000 19
43678 2001-07-01 00:00:00.000 20
43679 2001-07-01 00:00:00.000 21
43680 2001-07-01 00:00:00.000 22
43681 2001-07-01 00:00:00.000 23
43682 2001-07-01 00:00:00.000 24
43683 2001-07-01 00:00:00.000 25
43684 2001-07-01 00:00:00.000 26
43685 2001-07-01 00:00:00.000 27
43686 2001-07-01 00:00:00.000 28
43687 2001-07-01 00:00:00.000 29
43688 2001-07-01 00:00:00.000 30
43689 2001-07-01 00:00:00.000 31
43690 2001-07-01 00:00:00.000 32
43691 2001-07-01 00:00:00.000 33
43692 2001-07-01 00:00:00.000 34
43693 2001-07-01 00:00:00.000 35
----------------------------------------------------------------------------------------------------------------
带条件的：
select * from
(SELECT username,ROW_NUMBER() OVER (order by id)as RowNumber FROM users)a where RowNumber=2
---------------------------------------------------------------------
临时表的方法:
select IDENTITY(int,1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num between 10 and 20
