mssql 函数 SQL高级功能

SQL除了常见函数的学习外，随着学习的深入，让我们来学习一下SQL中的高级功能

1.窗口函数

窗口函数也称OLAP函数，可以对数据库数据进行实时分析处理

基本语法：

其中窗口函数位置可以放专用窗口函数和聚合函数，因为窗口函数是对where或者group by 函数执行结果后的操作，所以只能出现在selcet和order by 语句中

2.主要功能

如果我们相对班级成绩进行排名，则运行窗口函数

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

order by 成绩 desc)

as ranking from 班级表

得到以下结果，rank是排序函数，parition by 按班级分组，order by则按成绩进行排名。

看起来parition by 和 group by 视乎用法相似，但它们还是有区别的，

所以，窗口函数具有以下功能：

1.同时具备分组排序功能

2.不减少原表的行数

3.专用窗口函数

专用的窗口函数有rank，dense_rank和row_number

它们之间的区别是

排列顺序以及显示方式上的区别，rank显示的名词是5，5，5，8

而desenrank则是5，5，5，6，row_number是5，6，7，8

4.聚合函数

直接上例子

select *,sum(成绩) over (order by 学号) as current_sum,

avg(成绩) over (order by 学号) as current_avg,

count(成绩) over (order by 学号) as current_count,

max(成绩) over (order by 学号) as current_max,

min(成绩) over (order by 学号) as current_min from 班级表;

其中sum函数主要是对前n行的结果进行总和输出

4.1窗口函数移动平均

以AVG函数为例

select *,avg(成绩) over (order by 学号 rows 2 preceding)

as current_avg from 班级表;

其中用了row 2 preceding 的意思是之前n行，

配上图例方便理解。

5.各个函数使用场景

1）经典top N问题

找出每个部门排名前N的员工进行奖励

2）经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名

3）在每个组里比较的问题

比如查找每个组里大于平均值的数据，可以有两种方法：

方法1，使用前面窗口函数案例来实现

方法2，使用关联子查询

4）累计求和问题

6.存储过程

存储过程是将常用的SQL语句存储起来，方便下次使用，从而提高工作效率，避免重复操作。

定义存储过程的语法形式：

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

6.1无参数存储

语法里的begin...end用于表示sql语句的开始和结束。语法里面的?sql语句?就是重复使用的sql语句。下面通过一个例子看下如何使用。例如查出“学生表”里的学生姓名。

sql语句是：

select 姓名 from 学生表;

把这个sql语句放入存储过程的语法里，并给这个存储过程起个名字叫做就a_stuent1：

create procedure a_stuent1() begin select 姓名 from 学生表; end；

6.2有参数存储过程

2）有参数的存储过程

前面的存储过程名称后面是()，括号里面没有参数。当括号里面有参数时，就是下面的语法：

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

通过一个案例看下，现在要在“学生表”里查找出指定学号的学生姓名。如果指定学号是0001，那么sql语句是：

select 姓名 from 学生表 where 学号='0001';

现在问题来了，一开始不知道指定学号是哪一个，只有使用的时候才知道业务需求。比如今天要查找学号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。这样在使用存储过程的时候，给定参数的值就可以灵活的按业务需求来查询了。

比如现在要查询学号=0001的学生姓名，那么就在使用存储过程的参数中给出学号的值，也就是下面括号里的0001：

call getNum(0001);

（3）默认参数的存储过程

前面的存储过程名称后面是(参数1,参数2)，括号里面只包含参数的类型和名字，方便调用。存储过程还一种情况是有默认参数，是下面的语法。

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

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

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

注意事项：

1）定义存储过程语法里的SQL语句代码块必须是完整的sql语句，必须用“;”结尾

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

2）定义不同的存储过程，要使用不同的存储过程名称，相同的存储过程的名字会引起系统报错。

（编辑：武陵站长网）

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