在sql server2005中可以在聚合函数的后面使用over(partition by col)替换group by的写法。有时候这样写比group by的形式要简短一些。下面我用AVG为例说明一下：
下面的sql创建了一张student_class_grade的表，该表的三个字段分别为学生id，班级编号，成绩；我们需要查询系统中的所有同学的成绩，和班级的平均成绩。
if object_id('student_class_grade','U') is not null
drop table student_class_grade;
GO
create table student_class_grade
(
student_id int, --学生id
class_no int, --班级编号
grade int --成绩
);
GO
INSERT INTO student_class_grade VALUES(1,1,90);
INSERT INTO student_class_grade VALUES(2,1,85);
INSERT INTO student_class_grade VALUES(3,1,80);
INSERT INTO student_class_grade VALUES(4,1,80);
INSERT INTO student_class_grade VALUES(5,1,90);
INSERT INTO student_class_grade VALUES(6,1,75);
INSERT INTO student_class_grade VALUES(7,1,89);
INSERT INTO student_class_grade VALUES(11,2,90);
INSERT INTO student_class_grade VALUES(12,2,85);
INSERT INTO student_class_grade VALUES(13,2,80);
INSERT INTO student_class_grade VALUES(14,2,80);
INSERT INTO student_class_grade VALUES(15,2,90);
INSERT INTO student_class_grade VALUES(16,2,75);
INSERT INTO student_class_grade VALUES(17,2,100);
sql server2005中我们可以用简单的一个语句完成这个任务partition by 后面跟聚合列，如下：
select
student_id,class_no,grade
,class_avg_grade = AVG(grade) over(partition by class_no)
from student_class_grade
若是在2000中，我们不得不使用子查询，查询要复杂一些
select scg.student_id
,scg.class_no
,scg.grade
,t_avg. class_avg_grade from student_class_grade scg
INNER JOIN
(select class_no,class_avg_grade = AVG(grade) from student_class_grade group by class_no) t_avg
ON t_avg.class_no = scg.class_no
同样其他聚合函数SUM，COUNT，MAX，MIN也可以使用类似用法。
posted on 2008-05-27 12:38
玉开 阅读(1752)
评论(10) 编辑 收藏 所属分类:
数据库 、
sql server