1.SQL高阶功能:窗口函数
![0fd3d385107a9d9ec88f7775a53d948e.png](https://i-blog.csdnimg.cn/blog_migrate/405ad0c149a2703ddd993c44eb3d6bda.jpeg)
2.窗口函数重要知识点展开
2.1基本语法和如何使用窗口函数
2.1.1基本语法
<窗口函数> over (partition by (用于分组的列名)
order by(用于排序的列名))
2.1.2 如何使用
1)用partition by来分组。
2)用order by对分组后的结构进行排序。
*不用group by的原因:其分组汇总后会改变表的行数,一行只有一个类别
2.2分类和案例
![200b497db455c54bea7dffbbb563036f.png](https://i-blog.csdnimg.cn/blog_migrate/cbe8f6f6029686399b5e2b1564faa8b0.jpeg)
2.2.1专用窗口函数案例
rank(),dense rank(),row_number(
1)体现三者区别案例
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 班级表
查询结果如下:
![18915448926cb6bd5f5351693f8e29a6.png](https://i-blog.csdnimg.cn/blog_migrate/6ca4dd7aeee25c2fb8edcca6b7273d8e.jpeg)
三个函数的查询结果区别:
![5a47bf45ded5a04cb867afd835cb7f1f.png](https://i-blog.csdnimg.cn/blog_migrate/8d9a709f400b5c049efd1e06c511bebe.jpeg)
再来一个例子一眼看懂区别:
![1bca17790aabb27273de885cc8019caf.png](https://i-blog.csdnimg.cn/blog_migrate/c01e0f5111db8756bf376d0324ccd666.jpeg)
2)rank()-考虑并列+占用下个名次位置
【TOPN问题】编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分占用名词位置
![4f6376a5298151d3e22a67c0e011fcc8.png](https://i-blog.csdnimg.cn/blog_migrate/a60e9c5ecc17ad3d9bcbef7119ae5b3b.png)
![5bcf4548b66b43ad205d00814869b6c0.png](https://i-blog.csdnimg.cn/blog_migrate/d24408099c16ca757821268a66afa756.jpeg)
![00b43d04fa255aa2f77a9bb0fabb4924.png](https://i-blog.csdnimg.cn/blog_migrate/770c2a589f070f1ccf3e933c6eec77ad.png)
3)dense rank()-考虑并列+不占用下个名次位置
【TOPN问题】编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。
![4f6376a5298151d3e22a67c0e011fcc8.png](https://i-blog.csdnimg.cn/blog_migrate/a60e9c5ecc17ad3d9bcbef7119ae5b3b.png)
![8aed40e2973c55c60aabe7ae4fe5e289.png](https://i-blog.csdnimg.cn/blog_migrate/3f40ffc25734ffdbb1c263c99b325390.jpeg)
![0f7b37ed7c3a337cd0ccf9c273091213.png](https://i-blog.csdnimg.cn/blog_migrate/5f7e2357a7a35cdc2cdaab4bf63d7efe.jpeg)
4)row_number()-不考虑并列情况
【TOPN问题】:现有成绩表score,记录了每个学生各科的成绩。表内容如下。问题:查找每个学生成绩最高的2个科目
![4f6376a5298151d3e22a67c0e011fcc8.png](https://i-blog.csdnimg.cn/blog_migrate/a60e9c5ecc17ad3d9bcbef7119ae5b3b.png)
![adc874eadd69e335e59d364201244203.png](https://i-blog.csdnimg.cn/blog_migrate/34cb9dbbde28283e2564333bb8058fbc.jpeg)
![3c74d12b7e0c25686ee4eaf62ca59f62.png](https://i-blog.csdnimg.cn/blog_migrate/24754bfff669b4b1744f47c9e322c1f5.jpeg)
【TOPN万能模板】
![517bd271e846110469a6ea969e826570.png](https://i-blog.csdnimg.cn/blog_migrate/999fa65e23214e40b0ee6645e022455d.jpeg)
2.2.2聚合窗口函数和案例
![1ec19e9f8023f3a2b47571554c9f2309.png](https://i-blog.csdnimg.cn/blog_migrate/12e1b371fd304bad2641afdb2e2a64a0.jpeg)
1)sum,avg,count,max,min使用案例
【问题】在成绩表中对自身记录、及位于自身记录以上的数据进行求和,平均,计数,求最大值,最小值
![01ae4d2545d8f7f9e0bf84984ba911bb.png](https://i-blog.csdnimg.cn/blog_migrate/7ec48c15389ab35560cb448ea1b60b62.jpeg)
![0e2b1ed96989af8b88817df1d87c5029.png](https://i-blog.csdnimg.cn/blog_migrate/976870f6424240fe2316d0afc5589898.jpeg)
2)累计求和案例
“薪水表”中记录了员工发放的薪水。包含雇员编号,薪水,起始日期,结束日期四列。其中薪水即为该雇员在起始日期到结束日期这段时间内的薪水,当前薪水即满足(结束日期 = '9999-01-01')
![7bf6157c46a0060cbba77785964cc0e0.png](https://i-blog.csdnimg.cn/blog_migrate/e083d2e8825fd200ae31028421823c08.jpeg)
【问题1】按照雇员编号升序排列,查找薪水的累计和——累计薪水,其中累计薪水为前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和,其他以此类推
![fc3e1a760ad298d05625c0d2ddbea251.png](https://i-blog.csdnimg.cn/blog_migrate/f0bc08c67a7f0418824594c6d2572d48.jpeg)
![1267a3649a3e945d5e21c412fea5afb0.png](https://i-blog.csdnimg.cn/blog_migrate/c2c8caba733c241c7b8aac0136af1582.jpeg)
【问题2】根据确诊人数表按照日期进行升序排列,查找日期、确诊人数以及对应的累计确诊人数。
![de26947af2104b94a70e77072863ed84.png](https://i-blog.csdnimg.cn/blog_migrate/f923624f309ede26600e7bb93bdfa737.jpeg)
![a899d8ebcdf1e18ec0827e4df3921ad9.png](https://i-blog.csdnimg.cn/blog_migrate/17e42d3b3d1b2ec6a7d761b7ac11f442.jpeg)
![6274ed34ba60b352ab06894a131830a1.png](https://i-blog.csdnimg.cn/blog_migrate/7a1eedc0522406840e96379ccb317a86.jpeg)
3)如何在每个组里比较案例
【问题】查找单科成绩高于该科目平均成绩的学生名单
![095c00753b2753ce5b13e483b41b7d6d.png](https://i-blog.csdnimg.cn/blog_migrate/ebe84cee9823ba12b39055bc58eaf00a.jpeg)
![6a42d6b9717c405fcf5fa59b4cf7b403.png](https://i-blog.csdnimg.cn/blog_migrate/175609e9ac591a1e635d6f954ea6beb3.jpeg)
![a581904d5a80aa410d4aa380a5d5506c.png](https://i-blog.csdnimg.cn/blog_migrate/6244cacd30dcfe6603de759c68ad21b6.jpeg)
2.2.3 移动平均函数
![ac879898821f15a1bd6ea4c519457a2a.png](https://i-blog.csdnimg.cn/blog_migrate/f1e928a6b5c750eb08477ba0dba4640e.jpeg)
【案例问题】查询薪资表中在职员工的自己和前2个员工的平均薪资
![ec8866eb6320044f79df80bf0cf29c7b.png](https://i-blog.csdnimg.cn/blog_migrate/fd86557c01597c27daea85f565a5e204.jpeg)
![5aa05037f09783a82b001224ddcb6ec1.png](https://i-blog.csdnimg.cn/blog_migrate/f1da6a3576f5ffbc7d5449385b5ed44d.jpeg)
![137b3cda89793dbbe4be8d80eaf499d4.png](https://i-blog.csdnimg.cn/blog_migrate/4ece7f788c82fd0b3dcc1816eee0138b.jpeg)
2.3 存储过程
![ec6e81e17b106f8017899c91a8597e0b.png](https://i-blog.csdnimg.cn/blog_migrate/ec4de2b80e4ac31ceac59ebf1c2d7846.jpeg)
2.3.1 无参存储过程
![08d67a1490b1f5ce822ca714ba0a8e24.png](https://i-blog.csdnimg.cn/blog_migrate/d738d8dee5a11eda961614b30bdf10eb.jpeg)
在navicat里运行以后,建立的存储过程就会出现在下图的地方:
![43196309dcef1d6f8b9deb6460c41285.png](https://i-blog.csdnimg.cn/blog_migrate/9b5a267ed8dc82c16d49d85f7facbb32.jpeg)
2.3.2 有参存储过程
![2f97cd90d1c53d9cd7820cf36b5503ec.png](https://i-blog.csdnimg.cn/blog_migrate/038fe779963b8a69301fbd7070d55106.jpeg)
【对第3步进一步解释:】
一开始不知道指定学号是哪一个,只有使用的时候才知道业务需求。比如今天要查找学号0001,明天要查找学号002。这时候就需要用到参数,来灵活应对这种情况。把sql语句放入存储过程语法里就是:
create procedure getNum(num varchar(100)) begin select 姓名 from 学生表 where 学号=num; end;
其中getNum是存储过程的名称,后面括号里面的num varchar(100)是参数,参数由2部分组成:参数名称是num;参数类型是是varchar(100),这里表示是字符串类型。
存储过程里面的sql语句(where 学号=num)使用了这个参数num。这样在使用存储过程的时候,给定参数的值就可以灵活的按业务需求来查询了。
2.3.3默认参数存储过程
![6a132659a6c353f4208e31f8dda7c528.png](https://i-blog.csdnimg.cn/blog_migrate/7f4a346e5ad8ef3598f8a5b8cb98e307.jpeg)
1)in 输入参数
![52ad6f6e21250692ff4ddd2652d0c801.png](https://i-blog.csdnimg.cn/blog_migrate/85d7e7d8a096a58c3c549abc9e05cb11.jpeg)
2)out输出参数
![581023f9a694a5f98277b82c12b5e3ab.png](https://i-blog.csdnimg.cn/blog_migrate/9f7294dfb73af29c224ea220ab567a51.jpeg)
3)inout输入输出参数
![ee76172f94ffde5dc8f7cc737441b3fd.png](https://i-blog.csdnimg.cn/blog_migrate/9c2bf1864b87285000794eed16ef2eeb.jpeg)
2.3.4存储过程注意事项和作用
![6bffdc6c0b8ff7c7f148ba6c894e919c.png](https://i-blog.csdnimg.cn/blog_migrate/2464784f7b22b3b475f435c146d06506.jpeg)