Sql Server 开窗函数Over的使用实例详解_SqlServer_数据库_码蚁之家_www.codes51.com
返回首页
专题
网络编程
ASP教程 .NET教程 PHP教程 JSP教程 C#教程 Java教程 Delphi教程 VB教程 C/C++教程 Android开发 IOS开发 Windows Phone开发 Python教程 Ruby教程 移动开发 其他编程教程
网页制作
HTML教程 CSS教程 Dreamweaver教程 FrontPages教程 Javascript教程 web前端
数据库
SqlServer MySql Oracle Access DB2 SQLite 其他数据库
图形设计
photoshop教程 Fireworks教程 CorelDraw教程 Illustrator教程 AutoCad教程 FLASH教程
操作系统
Windows xp教程 Windows 7教程 Windows 8教程 Windows 2003教程 Windows Server 2008教程 Linux教程 Windows 10
网站运营
建站经验 SEO优化 站长心得 网赚技巧 网站推广 站长故事
手机学院
手机速递 安卓教程 iphone教程 手机评测 手机技巧 手机知识 手机应用 手机游戏 手机导购
网店宝典
开店指导 开店经验 网店装修 网店推广 网店seo 网购技巧
软件教程
办公软件 系统工具 媒体工具 压缩工具 图文处理 文件管理
范文之家
自我介绍 自我鉴定 写作模板 合同范本 工作总结 贺词祝福语 演讲致辞 思想汇报 入党申请书 实习报告 心得体会 工作计划 简历模板 工作报告 导游词 评语寄语 口号大全 策划书范文
信息工程
软件工程 企业开发 系统运维 软件测试
移民之家
移民动态 移民政策 移民百科 移民生活 技术移民 投资移民
知识大全
母婴 数码 摄影 装修 美文 常识 时尚 婚嫁 美食 养生 旅游 兴趣 职场 教育 文学 健康
问答大全
电脑网络 手机数码 QQ专区 生活 游戏 体育运动 娱乐明星 休闲爱好 文化艺术 社会民生 教育科学 健康医疗 商业理财 情感家庭 地区问题 其他
编程问答
IOS Android .NET Java C/C++ Delphi VC/MFC 其他语言 PHP MSSQL MYSQL Oracle 其他数据库 Web开发 Windows Linux 硬件/嵌入开发 网络通信 移动开发 云计算 企业IT 游戏开发
笑话大全
幽默笑话 爱情笑话 成人笑话 校园笑话 爆笑笑话 综合笑话 古代笑话 现代笑话 国外笑话

Sql Server 开窗函数Over的使用实例详解(1/2)

来源:互联网  时间:2018/8/8 8:20:53

利用over(),将统计信息计算出来,然后直接筛选结果集

declare @t table(
ProductID int,
ProductName varchar(20),
ProductType varchar(20),
Price int)
insert @t
select 1,'name1','P1',3 union all
select 2,'name2','P1',5 union all
select 3,'name3','P2',4 union all
select 4,'name4','P2',4

查询要求:查出每类产品中价格最高的信息

--做法一:找到每个组里,价格最大的值;然后再找出每个组里价格等于这个值的
--缺点:要进行一次join    

select t1.* from @t t1
 join (select ProductType, max(Price) Price from @t group by ProductType) t2 
 on t1.ProductType = t2.ProductType
 where t1.Price = t2.Price
 order by ProductType

--做法二:利用over(),将统计信息计算出来,然后直接筛选结果集。
--over() 可以让函数(包括聚合函数)与行一起输出。 

 ;with cte as(select *, max(Price) over(partition by (ProductType)) MaxPrice from @t)
select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice
 order by ProductType

-over() 的语法为:over([patition by ] <order by >)。需要注意的是,over() 前面是一个函数,如果是聚合函数,那么order by 不能一起使用。

--over() 的另一常用情景是与 row_number() 一起用于分页。

现在来介绍一下开窗函数

窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组,还可以同时返回基础行的列和聚合列。 

1.排名开窗函数

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。

排名开窗函数可以单独使用ORDER BY 语句,也可以和PARTITION BY同时使用。

PARTITION BY用于将结果集进行分组,开窗函数应用于每一组。

ODER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句。

例如查询每个雇员的定单,并按时间排序

;WITH OrderInfo AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number,
 OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) 
)
SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate
From OrderInfo WHERE Number BETWEEN 0 AND 10

窗口函数根据PARTITION BY语句按雇员ID对数据行分组,然后按照ORDER BY 语句排序,排名函数ROW_NUMBER()为每一组的数据分从1开始生成一个序号。

ROW_NUMBER()为每一组的行按顺序生成一个唯一的序号

RANK()也为每一组的行生成一个序号,与ROW_NUMBER()不同的是如果按照ORDER BY的排序,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如两个相同的行生成序号3,那么接下来会生成序号5。


上一篇MyBatis SQL xml处理小于号与大于号正确的格式
下一篇sql server中的任务调度与CPU深入讲解
明星图片
相关文章
《 Sql Server 开窗函数Over的使用实例详解》由码蚁之家搜集整理于网络,
联系邮箱:mxgf168#qq.com(#改为@)