从零开始学SQL——第七关SQL高级功能

【思维导图总结】

7.1 窗口函数

?窗口函数? over (partition by ?用于分组的列名?

order by ?用于排序的列名?)

！注意：窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

7.2 如何使用窗口函数

partition by分组后的结果称为“窗口"，即表示“范围”的意思

在每个班级内按成绩排名

select *,rank() over (partition by 班级

order by 成绩 desc) as ranking from 班级表

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

！注意与group by的区别

7.3 其他专用窗口函数

rank, dense rank, row number的区别：

7.4 案例：面试经典排名问题

7.5 案例：面试经典topN问题

Top N的问题其实就是问分组取每组最大值、最小值mssql 按关键字排序，每组最大的N条（top N）记录

7.5.1：按课程号分组取成绩最大值所在行的数据

7.5.2：按课程号分组取成绩最小值所在行的数据：和max同理

7.5.3：查找每个学生成绩最高的2个科目

！注意：报错的原因是为了忘记了运行顺序，where先跑的时候ranking还未执行，所以会报错

【举一反三】

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

本题的sql语句修改下（将where字句里的条件修改成N），就可以成为这类问题的一个万能模板，遇到这类问题往里面套就可以了：

# topN问题 sql模板 select * from (select *,row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking ?= N;

7.6 聚合函数作为窗口函数

7.7 案例：累计求和问题

按照雇员编号升序排列，查找薪水的累计和（累计薪水）。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和，其他以此类推。

1）从源数据导入excel的时候保存方式为xlsx，但导入失败，报错为无法读取。后来找到了下面这篇帖子将格式改完了xls，大家可以参考一下，感谢原作者。Excel导入Navicat时常碰到的问题

2）终于可以正常导入选sheet，结果还是没有导进去，只导进去了一条，看了报错日志，说是primary key的问题。我再回源数据一看，原来雇员编号不是唯一的，而在设置导入的时候我选了雇员编号为主键，这才出现了错误。果然一改完，顺利导入~

按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。

看到累计，就要想到用聚合函数作为窗口函数，用sum。

7.8 案例：如何在每个组里比较？

查找单科成绩高于该科目平均成绩的学生名单

7.9 窗口函数的移动平均

这个窗口函数的移动平均真的太高级了！高级在哪里呢？先来看一段query

用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均，即n+1行的平均。

7.10 窗口函数总结

7.11 存储过程

1）先定义存储过程

create procedure 存储过程名称() begin ?sql语句? ; end；

调用时：call 存储过程名称();

create procedure 存储过程名称(参数1,参数2,...) begin ?sql语句? ; end；

调用时：call getNum(参数);

1) in 输入参数:参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回。

2) out输出参数:参数初始值为空，该值可在存储过程内部被改变，并可返回

3) inout输入输出参数：参数初始值在存储过程前被指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回

（编辑：海南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!