SQL ServerSQL Server排名函数和排名开窗函数

啊是行函数?说实话我啊未殊懂,我晓得 order by
是排序用的,那么什么又是行函数呢?

联网下看几个示范就掌握了。

率先成立一个发明,随便插入一些数。

SQL Server 1

ROW_NUMBER
函数:直接排序,
ROW_NUMBER函数是上述升进行直接排序,并且因为连续的各个为各个一行数一个唯一的序号。(即行连)

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(order by U_Pwd))+'名' RowNum
4 from UserInfo

SQL Server 2

RANK 函数:并列排序,每当 order by
子句被指定的排列,如果回到一行数与另一行具有同等的价值,rank函数将被这些实践给相同的行数值。

于排名的经过中,保持一个中计数值,当值有所转时,排名序号将时有发生一个腾。(即行不连续)

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,rank() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

SQL Server 3

好明显的观看出4行数据并列第2名叫,然后直接就是是第6名为,这是因 order by
子句被指定的列 U_Pwd 的值相同。

DENSE_RANK
函数:并列排序,
旋即一点跟 RANK() 函数类似,order by
子句指定的排的价相同,排名数值相同,但是后面是接二连三的。(即行连)

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,DENSE_RANK() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

SQL Server 4

可以看到就是出4行数据并列第2名为,但是连下还是是第3叫做。

NTILE
函数:
拿查询的结果分发至指定数量之组中。
各个组有编号,编号从1上马。 对于各一样执行,NTILE 将回到此行所属之组的号码。

组中的行数计算方法吗 total_num_rows(结果集的母公司多次) /
num_groups(指定的组数)。

万一来余数 n,则前面 n
个组获得一个附加行。因此,可能无见面所有组都获得相当于数量之实施,但是组大小最充分但可能离开一行。

像,如果总行多次是 53,组数是 5,53 / 5
相当10不必要勤凡是3,按上面只规则就是,每组分配10执行,又盖余数为3,所以前面3组各组附加一行。

虽前三只组各组包含 11 行,其余两独组各组包含 10 行。

一边,如果总行多次而为组数整除,则行数将以组之间平均分布。
例如,如果总行多次为 50,有五只组,则每组将富含 10 行。

1 -- 以下是根据 U_Pwd 这一列进行分组
2 select *,
3 '第 '+convert(varchar,NTILE(3) over(order by U_Pwd))+' 组' RowNum
4 from UserInfo

SQL Server 5

本条发明中出10长条数据,指定分为3组,10/3对等给3余累1。

PS:排名函数后面要来 over() 子句。

 

行开窗函数:

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数,OVER()就是窗口函数。

窗口函数OVER()指定同组行,开窗函数计算起窗口函数输出的结果集中各行的价值。

开窗函数不待采取GROUP
BY就可针对数码进行分组,还可以同时返回基础实践的排列和聚合列。

排行开窗函数可以单独行使ORDER BY 语句,也堪同PARTITION BY同时采用。

ODER BY 指定排名开窗函数的逐条。在排名开窗函数中须采用ORDER BY语句。

PARTITION BY用于将结果集进行分组,开窗函数应用为各个一样组。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

SQL Server 6

因为 U_Pwd 这同样排有4栽不同的值,所以分为4组,然后 ROW_NUMBER
更在各国一样组中展开连续排序。

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

SQL Server 7

因为 U_Pwd 这同一列有4种植不同之价值,所以同样是分为4组,然后 RANK
再以各级一样组中进行排序,因为RANK是一视同仁排序,所以全部都是第一名。下面换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

SQL Server 8

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

SQL Server 9

因为 U_Pwd 这无异排有4种不同之值,所以一律是分为4组,然后
DENSE_RANK 再以各一样组中展开排序,因为DENSE_RANK也是一视同仁排序,所以全部都是第一叫做。下面换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

SQL Server 10

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 NTILE(3) 指定的组数分组,最后在根据 order by 子句指定的字段 U_Pwd 排序 
2 select *,
3 '第'+convert(varchar,NTILE(3) over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

SQL Server 11

因为 U_Pwd
这无异排有4栽不同之值,所以同样是分为4组。第1组发1条数据,所以便1单区。第2组有4长达数据,4/3等1余往往1,所以第2组分为3独区,又盖余数为1,所以第1单区附加1行。第3组有3漫长数据,3/3对等1余数为0,所以第3组来3个区。第4组发2修数,所以分吧2单区。

PS:在排序开窗函数中使 PARTITION BY
子句需要停放于 ORDER BY子句之前。

 

参考:

http://www.cnblogs.com/jhxk/articles/2531595.html

相关文章