sql开窗函数over()用法

最新推荐文章于 2024-08-03 23:10:08 发布

xueyong4712816

最新推荐文章于 2024-08-03 23:10:08 发布

阅读量1.3w

点赞数

分类专栏： SQL 文章标签： sql join 扩展

本文链接：https://blog.csdn.net/xueyong4712816/article/details/6663180

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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