posts - 206,  comments - 26,  views - 17万
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

 开窗函数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);

另一个例子

1 select * from
2 (select row_number() over(order by customerid asc) as rnumber,* from customers) as tbl
3 where tbl.rnumber between 15 and 20
posted on   努力--坚持  阅读(3104)  评论(0编辑  收藏  举报
(评论功能已被禁用)
编辑推荐:
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示