sql开窗函数over()用法

原创 2011年08月05日 15:17:08

over()的由来:

假设我们需要这样一个sql语句

SELECT FCITY, FAGE, COUNT(*)

FROM T_Person

WHERE FSALARY<5000

执行上面的 SQL 以后我们会得到下面的错误信息:

选择列表中的列 'T_Person.FCity' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 这是因为所有不包含在聚合函数中的列必须声明在 GROUP BY 子句中,可以进行如下修改:

SELECT FCITY, FAGE, COUNT(*)

FROM T_Person

WHERE FSALARY<5000

GROUP BY FCITY , FAGE

但是结果不是我们想要的,我们可以自己配凑这个

SELECT FCITY , FAGE ,

(

SELECT COUNT(* ) FROM T_Person

WHERE FSALARY<5000

)

FROM T_Person

WHERE FSALARY<5000

但是是不是是很罗嗦这时候开窗函数就又用武之地了

SELECT FCITY , FAGE , COUNT(*) OVER()

FROM T_Person

WHERE FSALARY<5000

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同,PARTITION BY 子句创建的分区是独立于结果集的,创建的分区只是供进行聚合计算的,而且不同的开窗函数所创建的分区也不互相影响。

 

总结:开窗函数能很方便让我们把聚集函数的值放到查询项中。

扩展:虽然开窗函数很方便,但是它也有它的弊端,会增加查询时间。具体怎么增加,说不清楚。

看看我做的例子:

  SELECT   ROW_NUMBER() OVER(ORDER BY m.ID) AS '序号'
        ,m.[ID]    AS '编码'  
       ,m.[Name] AS '零配件名称'
       ,CASE Imported WHEN 0 THEN '国产' ELSE '进口' END  AS '国产/进口'
       ,[TypeSpecification] AS '规格型号'
       ,[DrawingNo] AS '零配号/图号'
       ,MaterialUnit.Name AS '计量单位'
       ,b.收入数量
       ,b.收入金额
       ,b.支出数量
       ,b.支出金额
  FROM [SameWayAgileXYtest].[dbo].[Material] AS m
  JOIN SameWayAgileXYtest.dbo.MaterialUnit
  ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID
  JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout
  ON m.ID  = inout.MaterialID
  JOIN 
  (SELECT m.ID
  ,SUM([CurrentInQty])   AS '收入数量'
      ,SUM([CurrentInAmount])  AS '收入金额'
      ,SUM([CurrenOutQty])  AS '支出数量'
      ,SUM([CurrentOutAmount])    AS '支出金额'
  FROM [SameWayAgileXYtest].[dbo].[Material] AS m
  JOIN SameWayAgileXYtest.dbo.MaterialUnit
  ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID
  JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout
  ON m.ID  = inout.MaterialID
  WHERE ReportYear=2011 AND  ReportMonth <= 2
GROUP BY m.ID ) b
  ON b.ID =m.ID
  
--带开窗函数  
    SELECT   ROW_NUMBER() OVER(ORDER BY m.ID) AS '序号'
        ,m.[ID]    AS '编码'  
      ,m.[Name] AS '零配件名称'
      ,CASE Imported WHEN 0 THEN '国产' ELSE '进口' END  AS '国产/进口'
      ,[TypeSpecification] AS '规格型号'
      ,[DrawingNo] AS '零配号/图号'
      ,MaterialUnit.Name AS '计量单位'
      ,SUM([CurrentInQty]) OVER (PARTITION BY m.ID)   AS '收入数量'
      ,SUM([CurrentInAmount]) OVER (PARTITION BY m.ID)   AS '收入金额'
      ,SUM([CurrenOutQty]) OVER (PARTITION BY m.ID)   AS '支出数量'
      ,SUM([CurrentOutAmount]) OVER (PARTITION BY m.ID)     AS '支出金额'
  FROM [SameWayAgileXYtest].[dbo].[Material] AS m
  JOIN SameWayAgileXYtest.dbo.MaterialUnit
  ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID
  JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout
  ON m.ID  = inout.MaterialID
  WHERE ReportYear=2011 AND  ReportMonth <= 3
  

最后是43:57

  

 

SQL中over用法大全

OVER(PARTITION BY)函数介绍 开窗函数                Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对...
  • moshansk
  • moshansk
  • 2016年09月06日 16:52
  • 13987

SQL中over

1.over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。其参数:over(partition by columnname1 order by...
  • happyxzxin
  • happyxzxin
  • 2008年06月13日 23:01
  • 929

SQL中over的用法

在泡坛子的时候中无意中发现了这个函数,才知道oracle分析函数是如此的强大,其中over() 函数的用法又尤为的特别,所以将自己的研究结果记录一下。   个人理解:over() 函数 是对 分析函...
  • liuyuehui110
  • liuyuehui110
  • 2015年01月15日 09:01
  • 2676

over在SQL里就什么意思

转自:http://zhidao.baidu.com/question/89856341.html RANK ( ) OVER ( [query_partition_clause] order_by...
  • ArvinStudy
  • ArvinStudy
  • 2012年07月26日 18:00
  • 643

PL/SQL函数 over(partition by col)

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编...
  • yhb315279058
  • yhb315279058
  • 2014年12月17日 09:21
  • 2245

sql语句关键字over的作用及用法

在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句,用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。   窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行...
  • guochunyang
  • guochunyang
  • 2017年02月28日 15:17
  • 341

sql Over的用法

 over是用于数据的分区和排序,常用在函数后边使用 over 子句主要是2005以后出现的,主要使用在 聚合函数后面 行号: select row_number()over(order ...
  • hutao1101175783
  • hutao1101175783
  • 2014年01月17日 18:53
  • 302

sql中over函数介绍,收藏了

学习是枯燥的,所以作为一个学习者,要学会在学习中找到快乐,这样才能激发兴趣,兴趣是最好的老师,这样,学习就慢慢的变成了一件快乐的事情叻。原本一直做Oracle的管理和维护,对PLSQL不是很感兴趣,在...
  • courageously
  • courageously
  • 2010年07月29日 11:40
  • 5652

Sql server 开窗函数over()的语法

用法一:与ROW_NUMBER()函数结合用,给结果进行排序编号,如图:   代码如下: SELECT ROW_NUMBER() over(order by Requ...
  • hww9011
  • hww9011
  • 2017年02月21日 18:59
  • 811

sum()over()和count()over()分析函数

Sum()over()和count()over分析函数使用 创建测试表 create table test(sales_id varchar2(2),sales varchar2(10),de...
  • fang_fang_2008
  • fang_fang_2008
  • 2015年02月10日 09:42
  • 3476
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql开窗函数over()用法
举报原因:
原因补充:

(最多只允许输入30个字)