T-SQL over()简单举例说明

个人语言表达能力不太好， 所以还是结合用例来进行说明

数据表(one)：

id name num
1 ne 1
2 none 5
3 none 2
4 no 3
5 none 1
6 none 3
7 none 3

简易导航：

简单举例：

01.over()

T-SQL语句：select id, sum(num) over() as 'sum' from one

执行结果：

使用over()向结果中添加了一列(sum)， 该列为one表中num列的和。

若不适用over()，执行此语句：select id, sum(num) as 'sum' from one; 执行结果将会报错， 因为聚合函数sum()只能生成一个结果， 而不会为每一行添加该值。

02.over(partition by [column_name])

T-SQL语句：select id, name, sum(num) over(partition by name) as 'sum' from one

执行结果：

partition by [column_name]的意义是根据列名column_name进行分组。

从执行结果可以看出， sum(num) over(partition by name) 是先按name字段进行分组， 然后通过聚合函数sum()计算每个分组的num字段的和。

03.row_number() over(order by [column_name])

T-SQL语句：select row_number() over(order by id) as row_id, name from one

执行结果：

row_number() over(order by id)这段语句，可以简单理解为：根据id的排序生成一列排序序号列。

注意：over里的order by子句必需

04.row_number() over(partition by [column_name] order by [column_name])

T-SQL语句：select id, num, row_number() over(partition by num order by id) as 'row_id' from one

执行结果：

这个例子相对上个例子， 仅仅是在over中多加入了一个partition by子句， 其实对于多加入的partition by子句， 只需要理解为：需要先分组， 然后对每组分别执行后面的操作即可。

所以对于row_number() over(partition by num order by id) as 'row_id'， 只需要理解为先按num进行分组， 然后对每个分组按id进行排序，并生成序列号。如上图， num为1的为一组，然后按id进行排序，再生成对应的序列号， 所以id为1的在它所在的组中的序列号为1，而id为5的在它所在的组中的序列号为2。

05.rank() over(order by [column_name])

T-SQL语句：select id, name, num, rank() over(order by num) as 'rank_flag' from one

执行结果：

rank如同row_number，同样是生成一个序号，不同的是， rank会对over中指定的column_name进行检验，其值相同，则其序号也相同。

如结果所示，按num升序排序， 并切num值相同的， 其rank_flag序号也相同。 可能你已经发现，为什么没有2、5、6这些序号？其实它这里的需要， 你可以这么理解，假设该值为n，这就表示，它前面有n-1个记录比它大(或小，依据order by设置的排序方式)。如上图中第三条数据， 其rank_flag为3，这就表示， 按num升序排列时， 有(3-1)个记录比它小；同样的， 第七条数据的rank_flag值为7，这就表示按num升序排序时， 有(7-1)个记录比它小。

注意：这里的order by也是必需的。

06.rank() over(partition by [column_name] order by [column_name])

T-SQL语句：select id, name, num, rank() over(partition by name order by num) as 'rank_flag' from one

执行结果：

如果能理解例子4的partition by说明和5中关于rank的说明， 这里应该就很好看懂了。

这里首先按name进行分组，然后每组按照num进行排序。name值为'ne'和'no'的都只有一条记录，所以rank_flag直接为1，name值为'none'的有五条记录， 然后按照num进行排序， 为什么这组有两个为3序号的和为什么没有序号4， 这两个问题在这里就不再解释了， 可以参照例子5。

07.dense_rank() over(order by [column_name])

T-SQL语句：select id, name, num, dense_rank() over(order by num) as 'denseRank_flag' from one

执行结果：

dense_rank()，看名字就知道， 它的功能和rank()非常相似。 同样是生成需要， 和rank()一样，对于over(order by [column_name])中指定的column_name，若其值相同，则其序号也是相同的， 与rank()不同的地方， 也就是我们看rank()时非常奇怪的那个地方(序号不连贯)， dense_rank()生成的序号是连贯的， 这个序号所代表的的含义可以理解为：假设序号为n，表示有n-1个值比它小(或大，依据order by设置的排序方式)，这里说的是n-1个值， 而不是n-1个记录(可能有多条记录是同一个值)

dense_rank() over(order by num) as 'denseRank_flag'， 根据num进行排序， 相同值为同一序号。 结果中第七条记录，序号为4，表示有(4-1)个值比它小，列出来→1、2、3.

注意：这里的order by也是必需的。

08.dense_rank() over(partition by [column_name] order by [column_name])

T-SQL语句：select id, name, num, dense_rank() over(partition by name order by num) as 'denseRank_flag' from one

执行结果：

相信已经不用解释太多了， 根据partition by给出的name字段进行分组， 然后依据num进行排序，并以dense_rank()的方式给出对应序号。

09.ntile([number]) over(order by [column_name])

T-SQL语句：select id, name, num, ntile(3) over(order by id) as 'tileId' from one

执行结果：

ntile([number])表示对结果进行分组， 该列的值为组的ID， number表示需要分为number组。

你可能又看到了tileId为1的记录有三条， 而其它的组只有两条， 这实际是一种固定， 在不能完全分组时(记录数除以组数有余数时)， 余数会被依次分配到前面的组中。 比如有11条记录时， ntile指定分为三组， 11/3还余2， 也就是三组平均分三个时， 还会多出两个， 而这两个就会被分配到前两个组中，也就是分组时会按4:4:3的比例进行分配。

ntile(3) over(order by id) as 'tileId'，以id进行排序， 并按此次序分三组。

10.ntile([number]) over(partition by [column_name] order by [column_name])

T-SQL语句：select id, name, num, ntile(2) over(partition by num order by id) as 'tileId' from one

执行结果：

这里也不做太多说明了：先一num进行分组， 然后每组按照id进行排序并将其分为两组。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hourglasser/p/3351194.html