over()的用法

 开窗函数over的常用方法
-- 1、为每条数据显示聚合信息
-- 2、为每条数据提供分组的聚合函数结果
-- 3、与排名函数一起使用

 

-- 1 为每条数据显示聚合信息

-- 准备一些数据
-- 该查询表只能用在SQL Server 2008中
select * from
(
values
(1, '张三', 100),
(2, '李四', 87),
(3, '赵钱', 95),
(4, '孙李', 88)
) as tbl(stuId, stuName, stuScore);

-- 将上数据作为临时数据，求平均值
select avg(stuScore) as avgScore from
(
values
(1, '张三', 100),
(2, '李四', 87),
(3, '赵钱', 95),
(4, '孙李', 88)
) as tbl(stuId, stuName, stuScore);

-- 得到平均分为92分，如果需要显示出所有学员成绩，并在每个学员成绩后面加上平均分
select *, avg(stuScore) as avgScore from
(
values
(1, '张三', 100),
(2, '李四', 87),
(3, '赵钱', 95),
(4, '孙李', 88)
) as tbl(stuId, stuName, stuScore);
-- 报错，因为聚合函数只有一行数据，而学生有4条数据，两者不统一
-- 使用开窗函数
-- 语法
-- 聚合函数 over() as 别名
-- 修改SQL语句
select *, avg(stuScore) over() as avgScore from
(
values
(1, '张三', 100),
(2, '李四', 87),
(3, '赵钱', 95),
(4, '孙李', 88)
) as tbl(stuId, stuName, stuScore);

-- 2 为每条数据提供分组的聚合函数结果
-- 依旧是这些数据，但是为每个学员添加一个组别，数据如下
select * from
(
values
(1, '张三', 100, '组长'),
(2, '李四', 87, '学干'),
(3, '赵钱', 95, '组长'),
(4, '孙李', 88, '学干')
) as tbl(stuId, stuName, stuScore, stuOtherName);
-- 希望平均分还要分开求，即总平均分和所有学干的平均分和所有组长的平均分分别显示
-- 可以使用
-- 聚合函数 over(partition by 字段) as 别名
-- 即
select *,
avg(stuScore) over() as 平均分,
avg(stuScore) over(partition by stuOtherName) as 分组平均分
from
(
values
(1, '张三', 100, '组长'),
(2, '李四', 87, '学干'),
(3, '赵钱', 95, '组长'),
(4, '孙李', 88, '学干')
) as tbl(stuId, stuName, stuScore, stuOtherName);

-- 3 与排名函数一起使用
-- 使用row_number()函数可以为数据生成一个自动增长的数字列
-- 使用
-- row_number() over(order by 字段) as 别名
-- 表示这个自动增长的列使用"字段"的排序规则添加

-- 例如按照分数添加序号
select
row_number() over(order by stuScore) as 序号,
*,
avg(stuScore) over() as 平均分,
avg(stuScore) over(partition by stuOtherName) as 分组平均分
from
(
values
(1, '张三', 100, '组长'),
(2, '李四', 87, '学干'),
(3, '赵钱', 95, '组长'),
(4, '孙李', 88, '学干')
) as tbl(stuId, stuName, stuScore, stuOtherName);

-- 按照姓名添加序号
select
row_number() over(order by stuName) as 序号,
*,
avg(stuScore) over() as 平均分,
avg(stuScore) over(partition by stuOtherName) as 分组平均分
from
(
values
(1, '张三', 100, '组长'),
(2, '李四', 87, '学干'),
(3, '赵钱', 95, '组长'),
(4, '孙李', 88, '学干')
) as tbl(stuId, stuName, stuScore, stuOtherName);

另一个例子

select * from
(select row_number() over(order by customerid asc) as rnumber,* from customers) as tbl
where tbl.rnumber between 15 and 20