SQLServer中Partition By及row_number 函数使用详解

深入了解SQLServer中的Partition By及row_number函数

在SQL的世界中，partition by关键字是分析性函数的重要组成部分。它能够将结果集进行分组，让我们能够更灵活地处理数据。如果没有指定partition by，那么整个结果集将被视为一个分组。今天，我们来深入一下SQLServer中的Partition By以及与之息息相关的row_number函数。

让我们想象一个场景：我们需要查询出不同分类下的记录。你可能会首先想到使用Group By来进行分类，然后使用Order By来获取记录。在实际操作中，我们可能会遇到一些问题。

假设我们有一个表，其中的数据按照提交时间倒序排列。在这个表中，“corp_name”是分类的GUID。如果我们仅仅使用Group By，可能无法达到预期的效果。

这时，我们可以引入Partition By和row_number函数。Partition By也具有分组功能，它可以和row_number函数一起使用，来为我们提供每组的行数。这样，我们就可以轻松地获取每个分类的记录。

让我们通过一个例子来演示一下：

查询语句如下：

```sql

SELECT , ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) NUM FROM TESTDB

```

通过这个查询，我们可以看到结果中多出一列NUM。这个NUM列表示了相同行的个数，比如A1有3个，它就给每个A1标上是第几个。

这样，我们就可以轻松地获取每个分类的记录。只需要筛选出NUM等于1的记录即可。

深入ROW_NUMBER()函数的奇妙世界

当我们谈及数据库查询中的ROW_NUMBER()函数，它似乎只是单纯地给每一行数据标上一个序号。但实际上，这个函数的潜力远不止于此。让我们深入了解并它的几个实际应用场景。

基础应用：简单的行号标注

在基本的查询中，ROW_NUMBER()函数可以为查询结果中的每一行分配一个序号。例如：

```sql

SELECT A, B, ROW_NUMBER() OVER(ORDER BY A DESC) AS NUM FROM TESTDB;

```

这个查询将按照列A的降序排列数据，并为每一行分配一个序号。

进阶应用：分组与排序

ROW_NUMBER()函数真正的威力在于其与其他SQL功能的结合使用，特别是PARTITION BY和ORDER BY的组合。比如：

```sql

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) AS NUM FROM TESTDB;

```

这个查询首先按列A分组数据，然后在每个分组内按列B的降序排列并分配序号。这样，我们可以轻松地为每个分组的特定顺序的记录赋予序号。

实例

接下来，让我们通过几个实例来进一步理解ROW_NUMBER()函数的使用：

1. 按psd排序并编号：假设我们有一个客户表QT_Customer，我们想为每个客户的psd排序后的记录编号。可以这样写：

```sql

SELECT email, customerID, ROW_NUMBER() OVER(ORDER BY psd) AS rows FROM QT_Customer;

```

这将按照psd列对进行排序，并为每个客户的每条记录分配一个序号。

2. 订单按价格排序并编号：在订单表OP_Order中，我们可以按订单的总价格升序排序并为每条记录编号：

```sql

SELECT DID, customerID, totalPrice, ROW_NUMBER() OVER(ORDER BY totalPrice) AS rows FROM OP_Order;

```

这将帮助我们了解每个客户下了多少订单。我们还可以统计每个客户最近下的订单是第几次下的订单。使用ROW_NUMBER()函数配合子查询和分组操作，我们可以轻松实现这一功能。我们还可以筛选出客户第一次下的订单或统计每个客户的所有订单中购买的金额最小的订单以及客户是第几次购买等。这些操作都需要结合ROW_NUMBER()函数与其他SQL功能进行复杂的查询和计算。ROW_NUMBER()函数是SQL中一个非常强大的工具，它能帮助我们更方便地处理和分析数据。通过深入理解并灵活运用这个函数，我们可以更高效地处理各种复杂的查询需求。数据：从客户订单记录出发

```sql

SELECT FROM (

SELECT customerID, ROW_NUMBER() OVER(PARTITION BY customerID ORDER BY insDT) AS rows

FROM OP_Order

) AS tabs

WHERE rows = 1;

```

```sql

SELECT

ROW_NUMBER() OVER(ORDER BY insDT) AS rows,

customerID,

totalPrice,

DID

FROM OP_Order

WHERE insDT > '2011-07-22';

```

在这段代码中，我们先执行WHERE子句筛选出在2011年7月22日之后的订单记录，然后对这些记录进行编号。通过这样的操作，我们可以方便地进行数据的分页处理，更精准地获取我们需要的信息。 如同时间的指针指向未来的方向，我们的数据查询也在不断地向前发展，帮助我们在大数据的海洋中精准捕鱼。希望这些代码能够帮助你更好地理解SQL开窗函数的使用，让你的数据查询更加流畅、精准。