ROW_NUMBER() 函数是 SQL Server 2005 引入的一个重要特性,它使得数据库开发者能够在不支持 LIMIT 关键字的 SQL Server 中实现类似 MySQL 的分页效果。该函数可以为结果集中的每一行分配一个唯一的整数,从而允许我们筛选出需要的数据范围。
在 SQL Server 中,ROW_NUMBER() 函数的语法如下:
```sql
ROW_NUMBER() OVER([ <partition_by_clause>] <order_by_clause>)
```
这里,`<partition_by_clause>` 可以用来将结果集分割成多个分区,每个分区内部的行将独立进行编号。而 `<order_by_clause>` 用于指定在每个分区内部如何对行进行排序,以决定行号的分配顺序。
以下是一些使用 ROW_NUMBER() 函数的示例:
1. **基础用法**:按 `id` 升序排序,并获取 `MyTable` 中第20到30条记录:
```sql
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS rownum, *
FROM MyTable
) AS items
WHERE items.rownum BETWEEN 20 AND 30;
```
2. **按价格升序排序**:在 `OP_Order` 表中,按 `totalPrice` 排序并编号:
```sql
SELECT DID, customerID, totalPrice,
ROW_NUMBER() OVER(ORDER BY totalPrice) AS rows
FROM OP_Order;
```
3. **按客户分组并排序**:统计每个客户的所有订单,按 `totalPrice` 升序排序,并为每个客户编号:
```sql
SELECT ROW_NUMBER() OVER(PARTITION BY customerID ORDER BY totalPrice) AS rows,
customerID, totalPrice, DID
FROM OP_Order;
```
4. **统计最近订单的序号**:找出每个客户最近的订单是第几次下单:
```sql
WITH tabs AS (
SELECT ROW_NUMBER() OVER(PARTITION BY customerID ORDER BY totalPrice) AS rows,
customerID, totalPrice, DID
FROM OP_Order
)
SELECT MAX(rows) AS '下单次数', customerID
FROM tabs
GROUP BY customerID;
```
5. **寻找最小金额订单**:找出每个客户所有订单中金额最小的一次购买是第几次:
```sql
WITH tabs AS (
SELECT ROW_NUMBER() OVER(PARTITION BY customerID ORDER BY insDT) AS rows,
customerID, totalPrice, DID
FROM OP_Order
)
SELECT *
FROM tabs
WHERE totalPrice IN (SELECT MIN(totalPrice) FROM tabs GROUP BY customerID);
```
6. **获取每个客户的首次订单**:通过 `rows = 1` 来筛选出每个客户的首次下单记录:
```sql
WITH tabs AS (
SELECT ROW_NUMBER() OVER(PARTITION BY customerID ORDER BY insDT) AS rows, *
FROM OP_Order
)
SELECT *
FROM tabs
WHERE rows = 1;
```
7. **分页查询**:ROW_NUMBER() 函数常用于分页查询,例如获取 `OP_Order` 表的第二页数据(假设每页10条):
```sql
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY insDT) AS rownum, *
FROM OP_Order
) AS items
WHERE items.rownum BETWEEN 11 AND 20;
```
通过这些示例,我们可以看到 ROW_NUMBER() 函数在 SQL Server 中的强大功能,它不仅可以用于实现分页,还能在复杂的查询中进行行的排序和分组,从而提供更灵活的数据处理能力。