sql server partition分区与group by 分组

例子：在一个StudentScore表中，有序号ID，班级ClassId，学生姓名Name，性别Sex,语文成绩ChineseScore,数学成绩MathScore,平均成绩AverageScore等等一些学生成绩及个人信息字段

问一：每个班级中，平均成绩在60（含60）分以上的学生人数？

问二：每个班级中本次考试平均成绩是班级前三的同学的所有信息？

问三：每个班级中语文与数学都达到60（含60）分以上的同学的所有信息？

一、Group By 分组

select count(ClassId) from StudentScore where AverageScore>=60 group by ClassId

group by + 字段 A：根据字段A分组查询，通常group by 是与聚合函数连用的，因为当group by 存在时，select查询的字段要么是在group by子句中，要么存在聚合函数中，当select后字段独立时，编译器将会报错。错误例子：select A,count(B),C from tabel group by A; 错误原因：字段C既没有在group by子句中，也没有在聚合函数中

 

二、partition by分区函数（此处只是partition的一小部分功能）

因为要查看每个班级中达到条件的同学的所有信息，那么根据group by的特性可知，此处已不适合使用group by。

select * from (select Row_Number() Over(partition by ClassId order by AverageScore desc) rownum,* from StudentScore) Score where rownum>0 and rownum<4

解释：首先根据班级ClassId进行分区，在此基础上再对平均分AverageScore进行降序排列。

partition by 分区函数可以根据某字段分区并返回所有结果集，作为分区函数一般与Row_Number() over()排序一起连用，可实现分区排序的功能。个人觉得：主要是可以返回查询到的、包含所有字段的结果集。

---------------------------------------------------------

group by是对检索结果的保留行进行单纯分组，一般总爱和聚合函数一块用例如AVG（），COUNT（），max（），main（）等一块用。

partition by虽然也具有分组功能，但同时也具有其他的功能。 

它属于oracle的分析用函数。
sum() over (PARTITION BY ...) 是一个分析函数。 他执行的效果跟普通的sum ...group by ...不一样，它计算组中表达式的累积和，而不是简单的和。

转载于:https://www.cnblogs.com/jasonlai2016/p/10166975.html