sql使用聚合函数查询多列问题

常用的聚合函数

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1、学生表

一个班级有N组，每个组有N个学生，每个学生对应一个成绩 

2、查询单列（或者包含聚合列）

==需求：查询每组的最高成绩以及组号==

select GroupId,max(Score) as maxScore from student  GROUP BY GroupId;

这段语句在 SQLSERVER 和 MYSQL 中都是没有问题的

3、特殊需求

==需求：查询每组的最高成绩以及组号以及最好成绩学生的学号。==

执行下面的sql语句

select StudentId,GroupId,max(Score) as maxScore from student  GROUP BY GroupId;

• 1

在MYSQL中是没有问题的，因为MYSQL要查询的列不必包含在GROUP BY中。而在SQLSERVER中是有问题的，SQLSERVER中要查询的列必须包含在GROUP BY中。

在SQLSERVER中如何解决这个问题呢？就需要用到OVER了。（在MYSQL中是没有OVER的）

OVER是对指定字段进行分区，可以配合ROW_NUMBER()函数使用，该函数是显示该列的行号。

假设我们对每组进行分区，在每个分区中按成绩排序，那我们就得到了每组按成绩排好序的行号。

select StudentId,GroupId,Score,ROW_NUMBER() over(partition by GroupId order by Score desc) as rowIndex from student;

 

即如果倒序排序的话，最小值项的行号必定是1，所以我们只需要找到每个分区行号是1的列。

此处不能直接进行查询，因为where语句中是不能直接使用别名的。

select * from(select StudentId,GroupId,Score,ROW_NUMBER() over(partition by GroupId order by Score desc) as rowIndex from student) as b where b.rowIndex = 1;

本文转载自：https://blog.youkuaiyun.com/qq_34149805/article/details/70135981