自用自用自用,有错误请大佬指正!
117 第N高的薪水
学习临时变量的使用(以在表中插入排序号为例)
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN(
SELECT DISTINCT Salary
FROM(
SELECT
Salary, @r:=IF(@p=Salary, @r, @r+1) AS rnk, @p:=Salary
FROM
Employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY Salary, DESC)tmp
WHERE rnk=N
);
END
1)首先根据Salary字段将Employee表降序排序
2)(SELECT @r:=0, @p:=NULL)init,在临时表中初始化两个临时变量
3)扫描Employee表,若当前Salary值一开始初始化的@p相等,则@r赋值仍为@r;若不相等,则赋值为@r+1。因为Employee表已经排好序了,所以现在扫描的顺序是按降序排好的,每出现一个不相等的值就是排名+1
4)再把@p赋值为当前扫到的Salary值
这种以临时变量排序号的做法可以处理重复值问题,还可以用来处理分组排名的问题,喵鸭!
178 分数排名
学习窗口函数!参考资料
窗口函数的语法如下:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>) as <新列名>
和聚合函数的区别是,窗口函数不会减少原表的行数。(聚合函数会直接以分组为一列展示)
一、常用的专用窗口函数有:
1)rank():排序,重复值不会被算为新排名
2)dense_rank():排序,重复值被算为新排名
3)row_number():字面意思
针对这三个专用的窗口函数,括号内不用写参数。
二、聚合函数作窗口函数
<聚合函数窗口函数>(<需要分组的列名>)
over (order by <需要排序的列名>) as <新列名>
当聚集函数作窗口函数时,运算得到的是一个累加的结果。使用效果如下所示:
180 连续出现的数字
lead()窗口函数:lead(<列名>,<偏移行数>),可以复制出一列再向上平移n行
同时要注意SQL中没有连等,比如num1=num2=num3,能通过编译是因为先把num1=num2转换为true或false了,再和num3比较。要采用num1=num2 and num2=num3这种方式。