Mikel
[转载](原创)ROW_NUMBER()函数的使用 – InSky – 博客园.
ROW_NUMBER()函数是SQL 2005中新添的一个函数。通常它被用在分页的SQL语句中。
微软官方的对此函数的描述是：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
我对此的理解：利用此函数可以为表中的某个字段建立序列，从1开始。就是说，根据已存在的某列，利用此函数可建立一新列，新列是数字，按照已存在列的顺序从1开始。
上边可能说的不是很清楚，请结合下边这个例子来看吧！
<span class="lit">1USE</span><span class="pln"> </span><span class="typ">Northwind</span><span class="pln"></span><span class="lit">2GO</span><span class="pln"></span><span class="lit">3</span><span class="pln"></span><span class="lit">4</span><span class="pun">--</span><span class="pln"></span><span class="lit">5SELECT</span><span class="pln"> </span><span class="typ">OrderID</span><span class="pun">,</span><span class="pln"></span><span class="lit">6</span><span class="pln"> </span><span class="typ">CustomerID</span><span class="pun">,</span><span class="pln"></span><span class="lit">7</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pln"></span><span class="lit">8FROM</span><span class="pln"> dbo</span><span class="pun">.</span><span class="typ">Orders</span><span class="pln"> WITH</span><span class="pun">(</span><span class="pln">NOLOCK</span><span class="pun">)</span><span class="pln"></span><span class="lit">9</span><span class="pln"></span><span class="lit">10SELECT</span><span class="pln"> </span><span class="typ">OrderID</span><span class="pun">,</span><span class="pln"></span><span class="lit">11</span><span class="pln"> </span><span class="typ">CustomerID</span><span class="pun">,</span><span class="pln"></span><span class="lit">12</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pun">,</span><span class="pln"></span><span class="lit">13</span><span class="pln"> ROW_NUMBER</span><span class="pun">()</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">ORDER BY orderID DESC</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">字段编号</span><span class="pln"></span><span class="lit">14FROM</span><span class="pln"> dbo</span><span class="pun">.</span><span class="typ">Orders</span><span class="pln"> WITH</span><span class="pun">(</span><span class="pln">NOLOCK</span><span class="pun">)</span> |
第一个查询只是一个普通的查询，查询出Orders表中的3个字段的记录。其结果为：
第二个查询相比第一个查询仅仅是多了“ ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号 ”这段语句。我们来分析一下这段语句。
这段语句的作用就是利用ROW_NUMBER()函数根据OrderID这列来生成一个新的数据列，这列的名称为字段编号。然后我们根据ORDER BY orderID DESC来指定字段编号这列按照OrderID的逆序来生成，结果如下：
比较两个结果集，会发现第二个结果集是按照OrderID字段逆序的记录，其实也可看成是字段编号这个字段的正序排序，ROW_NUMBER()函数相当于为SELECT语句末尾加了ORDER BY子句，第二个SELECT语句其实等价于：
<span class="lit">1SELECT</span><span class="pln"> </span><span class="typ">OrderID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="typ">CustomerID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> ROW_NUMBER</span><span class="pun">()</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">ORDER BY </span><span class="typ">OrderID</span><span class="pln"> DESC</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">字段编号</span><span class="pln"></span><span class="lit">5FROM</span><span class="pln"> dbo</span><span class="pun">.</span><span class="typ">Orders</span><span class="pln"> WITH</span><span class="pun">(</span><span class="pln">NOLOCK</span><span class="pun">)</span><span class="pln"></span><span class="lit">6ORDER</span><span class="pln"> BY </span><span class="pun">字段编号</span><span class="pln">ASC</span> |
下边来看一个对ROW_NUMBER()函数的简单分页应用。
<span class="pln"> </span><span class="lit">1</span><span class="pun">--</span><span class="typ">Partition</span><span class="pln"> </span><span class="typ">Page</span><span class="pln"> </span><span class="typ">Demo</span><span class="pln"> </span><span class="lit">2</span><span class="com">/* 取出第- 200条的记录（记录须按OrderID排序）*/</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="lit">4WITH</span><span class="pln"> </span><span class="typ">OrdersByOrderIDASC</span><span class="pln"> AS </span><span class="lit">5</span><span class="pun">(</span><span class="pln"> </span><span class="lit">6</span><span class="pln"> SELECT </span><span class="typ">OrderID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">7</span><span class="pln"> </span><span class="typ">CustomerID</span><span class="pun">,</span><span class="pln"> </span><span class="lit">8</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pun">,</span><span class="pln"> </span><span class="lit">9</span><span class="pln"> ROW_NUMBER</span><span class="pun">()</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">ORDER BY orderID ASC</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">字段编号</span><span class="pln"></span><span class="lit">10</span><span class="pln"> FROM dbo</span><span class="pun">.</span><span class="typ">Orders</span><span class="pln"> WITH</span><span class="pun">(</span><span class="pln">NOLOCK</span><span class="pun">)</span><span class="pln"></span><span class="lit">11</span><span class="pun">)</span><span class="pln"></span><span class="lit">12</span><span class="pln"></span><span class="lit">13SELECT</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="lit">14FROM</span><span class="pln"> </span><span class="typ">OrdersByOrderIDASC</span><span class="pln"> WITH</span><span class="pun">(</span><span class="pln">NOLOCK</span><span class="pun">)</span><span class="pln"></span><span class="lit">15WHERE</span><span class="pln"> </span><span class="pun">字段编号</span><span class="pln">BETWEEN </span><span class="lit">100</span><span class="pln"> AND </span><span class="lit">110</span> |
首先把应用ROW_NUMBER()函数后的结果集存在一张临时表中，然后以字段编号这个字段为条件，使用BETWEEN关键字过滤相应的记录。
以上就是我对ROW_NUMBER()函数的一点粗浅认识，希望可以帮到大家！
