在数据库中找到排序后第二行的数据:
select salary as secondhighsalary from emplyee order by salary desc limit 1 offset 1 ;
limit 用法查询:
;SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 开始行 (包含开始行,0代表第一行), 行数(需要多少条数据)
在数据库中查询第k大的记录的函数(无则返回为空):
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE P INT DEFAULT N-1;
IF (P<0) THEN
RETURN NULL;
ELSE
RETURN (
SELECT IFNULL(
(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT P, 1
), NULL
) AS SecondHighestSalary
);
END IF;
END
涉及到排名问题,这里我们可以使用 窗口函数来实现,mysql8 中已经实现了这个功能;
举例:
已知的三种窗口函数:
- rank()
- dense_rank()
- row_number()
看一下测试效果:
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级
根据举例我们这里应该看清楚了:
- row_number : 完全按照行数排名,不会去管是否有成绩重复;
- rank : 他也是按照行数排名,不过这里的重复的成绩的标号一样
- dense_rank : 重复的成绩标号一样,下一个成绩会在此基础上加一;
窗口函数:
什么是窗口函数?
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
那么语法中的<窗口函数>都有哪些呢?
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
举例
select *,
rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表
选择出logs表中num字段数值相等并且id连续的num值:
select l1.Num AS ConsecutiveNums from Logs l1,Logs l2,Logs l3 where l1=l2-1 and l2=l3-1 and l1.num=l2.num and l2.num =l3.num ;