参考:【1】https://www.cnblogs.com/DataArt/p/9961676.html 介绍的非常详细
MySQL 8.0后开始有窗口函数的。
窗口函数是作用域于每条语句上的,会返回相同数据量的行数。聚合函数会根据计算结果返回一个值。
窗口函数的一般格式:
function_name()over(write SQL)
按照功能划分,可以把MySQL支持的窗口函数分为如下几类:
-
序号函数:row_number() / rank() / dense_rank()
-
分布函数:percent_rank() / cume_dist()
-
前后函数:lag() / lead()
-
头尾函数:first_val() / last_val()
-
其他函数:nth_value() / nfile()
在题目中的应用:
【1】176. 第二高的薪水 因为leetcode的Mysql不是8.0的,所以我用了一下 SQL Server。
select
ISNULL(
(
select distinct a.Salary
from
(select DENSE_RANK()over(order by Salary desc ) row_no,Salary
from Employee ) as a
where
a.row_no=2
),NULL) AS "SecondHighestSalary"
【2】 177. 第N高的薪水 用了窗口函数
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
/* Write your T-SQL query statement below. */
select distinct Salary
from(
select dense_rank()over(order by Salary desc ) row_no,Salary
from Employee) t
where t.row_no=@N
);
END
row_number() 从1排到最后,不管里面的排序的关系。
rank() 不连续的数字排序,管里面的顺序,但是会计算每个重复元素的个数。
dense_rank() 连续的数字排序,会将大小相同的,排成序号相同的。
【3】178. 分数排名
select Score,
dense_rank()over(order by Score desc ) as 'Rank'
from Scores
select a.Department,a.Employee,a.Salary
from (
select ee.Name as Employee,de.Name as Department,
dense_rank() over(partition by ee.DepartmentId order by ee.Salary desc ) as row_no,ee.Salary
from Employee as ee inner join Department as de on ee.DepartmentId=de.Id
) a
where a.row_no=1
/* Write your T-SQL query statement below */
select de.NAME Department,a.nAME Employee,a.Salary
from (
select Name,DepartmentId,Salary,
dense_rank() over(partition by DepartmentId order by Salary desc ) as row_no
from Employee
) a join Department de on a.DepartmentId=de.Id
where a.row_no<=3