SQL Server中Group by语句与HAVING语句的使用

一、GROUP BY

GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, MAX.)联合使用来得到一个或多个列的结果集。

语法如下

SELECT column1, column2, ... column_n, aggregate_function (expression)            

FROM tables            

WHERE predicates            

GROUP BY column1, column2, ... column_n;

举例

新建tb_student表并向表中插入测试数据。

问题1

按照班级ID对学生进行分组，并查询出每个班级的学生数，学生的最大年龄，最小年龄，年龄总和及年龄平均等信息。

解答

SELECT s.class_id AS 班级,COUNT(*) AS 人数,MAX(s.student_age) AS 年龄最大,MIN(s.student_age) AS 年龄最小,SUM(s.student_age) AS 年龄总和,AVG(s.student_age) AS 年龄平均 FROM tb_Student AS s GROUP BY s.class_id

查询结果

问题2

查询出存在年龄小于25周岁学生的班级ID及班级人数。

解答

SELECT s.class_id AS 班级,COUNT(*) AS 人数 FROM tb_Student AS s GROUP BY s.class_id HAVING MIN(s.student_age)<25

查询结果

二、HAVING

HAVING语句是在GROUP BY语句之后使用，对分组的数据进行过滤。 所以，有GROUP BY语句才能有HAVING子句。

语法如下

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;