sql开窗函数over()用法

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

  

 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页