CT ClassID, AVG(Achievement) AS AvgAch
FROM Students
GROUP BY ClassID) AS S2 --计算每个班级的平均成绩
ONS1.ClassID = S2.ClassID;
查询结果如表9-10所示。
表9-10 查询每名学生成绩与本班级平均成绩的差异
| ClassID |
StudentName |
Achievement |
AvgAch |
Diff |
| 1 |
Grace |
99.00 |
90.500000 |
8.500000 |
| 1 |
Andrew |
99.00 |
90.500000 |
8.500000 |
| 1 |
Janet |
75.00 |
90.500000 |
-15.500000 |
| 1 |
Margaret |
89.00 |
90.500000 |
-1.500000 |
| 2 |
Steven |
86.00 |
83.000000 |
3.000000 |
| 2 |
Michael |
72.00 |
83.000000 |
-11.000000 |
| 2 |
Robert |
91.00 |
83.000000 |
8.000000 |
| 3 |
Laura |
75.00 |
85.250000 |
-10.250000 |
| 3 |
Ann |
94.00 |
85.250000 |
8.750000 |
| 3 |
Ina |
80.00 |
85.250000 |
-5.250000 |
| 3 |
Ken |
92.00 |
85.250000 |
6.750000 |
在使用OVER子句的情况下，查询语句会简洁许多，下面语句的查询结果与表9-10相同。
SELECT ClassID,
StudentName,
Achievement,
AVG(Achievement) OVER(PARTITION BY ClassID) AS AvgAch,
Achievement - AVG(Achievement) OVER(PARTITION BY ClassID) AS Diff
FROM Students;
虽然语句有所简洁，但是在性能方面该语句不如上面的联接方式。查询优化器为该语句生成的查询计划比较复杂，与联接语句在同一个批中执行时，含有OVER子句的查询开销占了66%，如图9-3所示。
图9-3 联接方式与OVER子句的性能比较