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 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

 

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

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

看看我做的例子：

[sql]  view plain  copy

1.   SELECT   ROW_NUMBER() OVER(ORDER BY m.ID) AS '序号'  
2.         ,m.[ID]    AS '编码'    
3.        ,m.[Name] AS '零配件名称'  
4.        ,CASE Imported WHEN 0 THEN '国产' ELSE '进口' END  AS '国产/进口'  
5.        ,[TypeSpecification] AS '规格型号'  
6.        ,[DrawingNo] AS '零配号/图号'  
7.        ,MaterialUnit.Name AS '计量单位'  
8.        ,b.收入数量  
9.        ,b.收入金额  
10.        ,b.支出数量  
11.        ,b.支出金额  
12.   FROM [SameWayAgileXYtest].[dbo].[Material] AS m  
13.   JOIN SameWayAgileXYtest.dbo.MaterialUnit  
14.   ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID  
15.   JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout  
16.   ON m.ID  = inout.MaterialID  
17.   JOIN   
18.   (SELECT m.ID  
19.   ,SUM([CurrentInQty])   AS '收入数量'  
20.       ,SUM([CurrentInAmount])  AS '收入金额'  
21.       ,SUM([CurrenOutQty])  AS '支出数量'  
22.       ,SUM([CurrentOutAmount])    AS '支出金额'  
23.   FROM [SameWayAgileXYtest].[dbo].[Material] AS m  
24.   JOIN SameWayAgileXYtest.dbo.MaterialUnit  
25.   ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID  
26.   JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout  
27.   ON m.ID  = inout.MaterialID  
28.   WHERE ReportYear=2011 AND  ReportMonth <= 2  
29. GROUP BY m.ID ) b  
30.   ON b.ID =m.ID  
31.     
32. --带开窗函数    
33.     SELECT   ROW_NUMBER() OVER(ORDER BY m.ID) AS '序号'  
34.         ,m.[ID]    AS '编码'    
35.       ,m.[Name] AS '零配件名称'  
36.       ,CASE Imported WHEN 0 THEN '国产' ELSE '进口' END  AS '国产/进口'  
37.       ,[TypeSpecification] AS '规格型号'  
38.       ,[DrawingNo] AS '零配号/图号'  
39.       ,MaterialUnit.Name AS '计量单位'  
40.       ,SUM([CurrentInQty]) OVER (PARTITION BY m.ID)   AS '收入数量'  
41.       ,SUM([CurrentInAmount]) OVER (PARTITION BY m.ID)   AS '收入金额'  
42.       ,SUM([CurrenOutQty]) OVER (PARTITION BY m.ID)   AS '支出数量'  
43.       ,SUM([CurrentOutAmount]) OVER (PARTITION BY m.ID)     AS '支出金额'  
44.   FROM [SameWayAgileXYtest].[dbo].[Material] AS m  
45.   JOIN SameWayAgileXYtest.dbo.MaterialUnit  
46.   ON m.UnitID = SameWayAgileXYtest.dbo.MaterialUnit.ID  
47.   JOIN SameWayAgileXYtest.dbo.[MonthlyInOutInventory] AS inout  
48.   ON m.ID  = inout.MaterialID  
49.   WHERE ReportYear=2011 AND  ReportMonth <= 3  
50.     

最后是43:57