mysql开窗函数over_sql中的开窗函数over()

今天刷LeetCode的时候看到一道题,题目是这个样子mysql

LeetCode上面要求是用mysql来解决这道题,由于平时我上班的时候大部分时间都是在sqlserver上操做,因此一看到这个题目的要求我脑海中就闪过了开窗函数当中的密集排名。不过这道题要求是用mysql来解决,因此我先将mysql上面的解决思路理一下。sql

mysql来解决这个问题的话首先想到的就是order by Score desc来将第一列完成,第二列的话个人想法是去重利用聚合函数count来给这些值从新排序。函数

SELECT

Score,

(SELECT count(0) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank

FROM Scores

sqlserver

ORDER BY Score descspa

上面就是mysql的解决方法,sqlserver的话就简单许多了,直接利用密集排名就能够搞定。server

select Score,dense_rank() over(order by Score desc) from Scoresblog

sqlserver的开窗函数分两大类,一类是聚合开窗函数,一类是排序开窗函数。排序

聚合类开窗函数聚合函数(列)over(选项)，选项能够是partition by子句,但不能够是order by 子句。it

over关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL标准容许将全部聚合函数用作聚合开窗函数。举个例子,开窗函数count(*) over() 对于查询结果的每一行都返回全部符合条件的行的条数。over关键字后的括号中还常常添加选项用于改变进行聚合运算的窗口范围。若是over关键字后的括号中的选项为空的话,则开窗函数会对结果集中的全部数据进行聚合运算。io

开窗函数的over关键字后面括号中可使用parttition by 子句来定义行的分区来进行聚合运算。与group by 子句不一样，partition by 子句建立的分区是独立于结果集的，建立的分区是进行聚合运算的，而不一样的开窗函数所建立的分区互不干扰。举个例子,下面的sql语句用于显示每个人员信息以及所在的班级和这个班级所拥有的人数：

select id,name,class,score,count(name) over(partition by class) from #students

over(partition by class)表示对结果集按照class进行分区,并计算当前所属的组的聚合计算结果。在同一个select语句中能够同时使用多个开窗函数。

排序开窗函数:row_number(行号),rank(排名),dense_rank(密集排名),ntile(分组排名)

直接看下面的sql可能会比较清晰:

select id,name,class,score,

row_number() over(order by score) as rownum,

rank() over(order by score) as rank,

dense_rank() over(order by score) as dense_rank,

ntile(6) over(order by score) as ntile from #students

对于row_number()来讲,就是得出排序结果的序号。

对于rank()来讲,就是获得排序结果的排名号,若是有两个第二名的话,就不会有第三名,有三个第二名就不会有第四名。

对于dense_rank()来讲,就是每一个人只有一种排名,而后出现两个两个并列第一的状况,这时候排在第一名后面的人就是第二名。

对于ntile(6)来讲,就是分红6等分而后分红6个组,并显示组所在的序号。

排序函数和聚合开窗函数相似,均可以在over子句中使用partition by子句 。