sqlserver查询第2-4行数据



法一：

Select ID,OrderDate,row_num from

(select *,row_number() over (order by OrderDate)as row_num from row_number) collection

where row_num between 2nd 4

row_num列是由row_number()函数生成的

法二：

With Collection as

(selectSalesOoderID,OrderDate,row_number() over(order by OrderDate)as RowNumber from row_number)

Select * from Collection where RowNumber between 2 and 4

(1)order by OrderDate默认为升序排列(asc)，降序排列为desc

(2)datetime类型值为 2015/7/2112:21:00

(3)with的写法叫公用表表达式

(4)row_number()over(partition by COL1 order by COL2)表示根据COL1分组，在组内根据COL2排序，而此函数计算的值就表示每组内部排序后的编号(组内连续唯一)