我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Sql Server中如何使用Over()函数

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

Sql Server中如何使用Over()函数

今天就跟大家聊聊有关Sql Server中如何使用Over()函数,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。

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

declare @t table(ProductID int,ProductName varchar(20),ProductType varchar(20),Price int)insert @tselect 1,'name1','P1',3 union allselect 2,'name2','P1',5 union allselect 3,'name3','P2',4 union allselect 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 ,OrderDateFrom OrderInfo WHERE Number BETWEEN 0 AND 10

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

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

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

DENSE_RANK()和RANK()类似,不同的是如果有相同的序号,那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3,那么接下来生成的序号还是4。

NTILE (integer_expression) 按照指定的数目将数据进行分组,并为每一组生成一个序号。

2.聚合开窗函数

很多聚合函数都可以用作窗口函数的运算,如SUM,AVG,MAX,MIN。

聚合开窗函数只能使用PARTITION BY子句或都不带任何语句,ORDER BY不能与聚合开窗函数一同使用。

例如,查询雇员的定单总数及定单信息

 WITH OrderInfo AS(SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK))SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID

如果窗口函数不使用PARTITION BY 语句的话,那么就是不对数据进行分组,聚合函数计算所有的行的值

 WITH OrderInfo AS ( SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) )

看完上述内容,你们对Sql Server中如何使用Over()函数有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

Sql Server中如何使用Over()函数

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

Sql Server中REPLACE函数如何使用

SQL Server中的REPLACE函数用于在一个字符串中替换指定的字符或字符串。REPLACE函数的语法如下:REPLACE ( 字符串表达式, 查找字符串, 替换字符串 )其中:- 字符串表达式是要进行替换操作的字符串。- 查找字符串
2023-08-15

SQL如何使用ROW_NUMBER() OVER函数生成序列号

这篇文章给大家介绍SQL如何使用ROW_NUMBER() OVER函数生成序列号,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER
2023-06-22

sql中over函数是做什么用的

sql over 函数用于对分组数据计算累积值、移动平均值和其他统计信息。它按分组表达式分区,按排序表达式计算值顺序,并按范围、行数或组数确定聚合范围。常见用途包括计算累积值、创建移动平均值和对行集进行排名。SQL 中 OVER 函数的作用
sql中over函数是做什么用的
2024-05-12

Sql Server中REPLACE函数的使用

在SQL Server中,REPLACE函数用于替换字符串中的指定字符或子字符串。REPLACE函数的语法如下:REPLACE (string_expression, search_string, replacement_string)其中
2023-08-15

​SQL Server中quotename()函数怎么使用

在SQL Server中,QUOTENAME()函数用于将一个标识符(如表名、列名等)包围在方括号中,以防止引起语法错误或与关键字冲突。QUOTENAME()函数的语法如下所示:QUOTENAME ( 'string' [, 'quot
2023-10-23

SQL Server中DateDiff函数怎么使用

SQL Server中的DateDiff函数用于计算两个日期之间的时间差。它的语法如下:```DATEDIFF ( datepart , startdate , enddate )```其中,datepart是指定时间单位的参数,可以是以下
2023-09-07

SQL Server中row_number函数怎么使用

在SQL Server中,ROW_NUMBER()函数用于给结果集中的每一行分配一个唯一的顺序号。它的基本语法如下:```ROW_NUMBER() OVER (ORDER BY column1, column2, ...)```其中,`RO
2023-08-14

SQL Server中的数学函数怎么使用

这篇“SQL Server中的数学函数怎么使用”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“SQL Server中的数学函数
2023-06-30

SQL Server中的JSON函数怎么使用

这篇文章主要介绍“SQL Server中的JSON函数怎么使用”,在日常操作中,相信很多人在SQL Server中的JSON函数怎么使用问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”SQL Server中的J
2023-06-30

Oracle中的over()函数怎么使用

今天小编给大家分享一下Oracle中的over()函数怎么使用的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。1 over()
2023-07-05

SQL Server DATEDIFF() 函数怎么使用

本篇内容主要讲解“SQL Server DATEDIFF() 函数怎么使用”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“SQL Server DATEDIFF() 函数怎么使用”吧!定义和用法D
2023-06-22

编程热搜

目录