SQL Server温故系列(5)：SQL 查询之分组查询 GROUP BY
1、group by 与聚合函数
group by 是一种能将查询结果划分为多个行组的查询语句的子句，其目的通常是为了在每个组上执行一个或多个聚合运算，所以 group by 通常会与聚合函数一块儿出现在查询语句中。
group by 的标准分组方式是按所有分组字段的值依次来分组。假如字段 a 的值有 3 种，字段 b 的值有 2 种；如果是group by a，那么就会被分为 3 组；而如果是group by a,b，那么就会先被 a 分为 3 组，然后这 3 组又会被 b 再各自分为 2 组，最终会被分为 3×2 等于 6 组。
显然，group by b,a最终也会被分为 6 组，换而言之，标准分组时的字段的顺序不会对分组结果产生影响。但分组字段的顺序会影响查询结果的排序，如果想要改变结果集的排序，可以通过 order by 子句来实现。
示例一、查询统计学生 1、2、3 的第 1 次考试成绩，且按各科总分降序排列：
select t.studentid,count(1) 科目数,
sum(t.scores) 总分,max(t.scores) 最高分,min(t.scores) 最低分,avg(t.scores) 平均分
from t_examresults t
where t.counts = 1 and t.studentid in(1,2,3)
group by t.studentid
order by 总分 desc;
示例二、查询统计学生 1、2、3 的第 1 次考试成绩，且按班级名称和学生名称来升序排列：
select t1.code,t1.name,t3.name,count(1) 科目数,
sum(t2.scores) 总分,max(t2.scores) 最高分,min(t2.scores) 最低分,avg(t2.scores) 平均分
from t_students t1
join t_examresults t2 on t1.id = t2.studentid and t2.counts = 1
join t_classes t3 on t1.classid = t3.id
where t1.id in(1,2,3)
group by t1.code,t1.name,t3.name
order by t3.name,t1.name desc;
注意：在含有 group by 子句的查询语句中，每组只会返回一行数据，且查询选择列表中的列只能是 group by 中的字段或聚合函数表达式。
2、group by 与 having
having 子句的作用有点类似于 where 子句，说到底它们都是过滤数据用的，但不同的是，where 子句过滤的最小单位是数据行，而 having 子句过滤的最小单位是行组。相较于 where 子句，having 子句最大的优势就是支持聚合函数。
having 子句只能在查询语句中使用，且通常与 group by 子句一起使用。如果查询语句中没有 group by 子句，那么就会有隐式的单一行组，但这通常是没有意义的。例如要查询统计平均分达到 80 分的学生第 1 次考试成绩，且按总分倒序排列，示例如下：
select t.studentid,count(1) 科目数,
sum(t.scores) 总分,max(t.scores) 最高分,min(t.scores) 最低分,avg(t.scores) 平均分
from t_examresults t
where t.counts = 1
group by t.studentid
having avg(t.scores) >= 80
order by sum(t.scores) desc;
3、group by 扩展分组
在实际的开发工作中，尤其是开发数据报表，往往需要统计多维度的小计和合计。大多数情况下用 union 也能达到类似效果，但实现起来比较繁琐，灵活性较差，性能往往也比较低。针对这类需求，sql server 提供了几个实用的扩展分组，以便能更好的实现这些需求。
3.1、group by rollup
rollup 是对 group by 子句的一种扩展，它允许计算标准分组及部分维度的小计及合计。rollup 的计算结果与分组字段的顺序有关，因为它的分组过程具有方向性，先计算标准分组，然后从右到左递减计算更高一级的小计，直到所有字段被计算完，最后计算合计。
对于group by rollup(a,b,c)，结果具有 (a,b,c)、(a,b,null)、(a,null,null)、(null,null,null) 唯一值的组。换而言之，group by rollup(a,b,c)的结果集就等价于group by a,b,c的结果集，加上group by a,b的结果集，再加上group by a的结果集，最后加上不带group by的总计结果集。
示例一、查询统计 1、2、3 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid
from t_students t
where t.classid in(1,2,3)
)
select t.classid,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by rollup(t.classid);
查询结果如下：
classid 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- 1 10 20 15 2 9 20 16 3 9 21 15 null 28 21 15
示例二、查询统计 1、2 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid,t.gender
from t_students t
where t.classid in(1,2)
)
select t.classid,t.gender,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by rollup(t.classid,t.gender);
查询结果如下：
classid gender 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- ----------- 1 0 6 19 15 1 1 4 20 18 1 null 10 20 15 2 0 4 20 17 2 1 5 20 16 2 null 9 20 16 null null 19 20 15
示例三、查询统计 1、2 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid,t.gender
from t_students t
where t.classid in(1,2)
)
select t.classid,t.gender,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by t.classid,rollup(t.gender);
查询结果如下：
classid gender 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- ----------- 1 0 6 19 15 1 1 4 20 18 1 null 10 20 15 2 0 4 20 17 2 1 5 20 16 2 null 9 20 16
3.2、group by cube
cube 是对 group by 子句的一种扩展，它允许计算标准分组及所有维度的小计及合计。cube 会对所有可能的分组进行统计，从而生成交叉报表。cube 比 rollup 的分组更多，完全包含了 rollup 的统计结果，且计算结果与分组字段的顺序无关，但如果字段顺序不同，默认的结果集排序会有不同。
对于group by cube(a,b)，结果具有 (a,b)、(a,null)、(null,b)、(null,null) 唯一值的组。换而言之，group by cube(a,b)的结果集就等价于group by a,b的结果集，加上group by a的结果集，再加上group by b的结果集，最后加上不带group by的总计结果集。
示例一、查询统计 1、2、3 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid
from t_students t
where t.classid in(1,2,3)
)
select t.classid,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by cube(t.classid);
查询结果如下：
classid 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- 1 10 20 15 2 9 20 16 3 9 21 15 null 28 21 15
示例二、查询统计 1、2 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid,t.gender
from t_students t
where t.classid in(1,2)
)
select t.classid,t.gender,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by cube(t.classid,t.gender);
查询结果如下：
classid gender 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- ----------- 1 0 6 19 15 2 0 4 20 17 null 0 10 20 15 1 1 4 20 18 2 1 5 20 16 null 1 9 20 16 null null 19 20 15 1 null 10 20 15 2 null 9 20 16
示例三、查询统计 1、2 班的学生个数及年龄：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid,t.gender
from t_students t
where t.classid in(1,2)
)
select t.classid,t.gender,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by t.classid,cube(t.gender);
查询结果如下：
classid gender 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- ----------- 1 0 6 19 15 1 1 4 20 18 1 null 10 20 15 2 0 4 20 17 2 1 5 20 16 2 null 9 20 16
3.3、group by grouping sets
grouping sets 是对 group by 子句的一种扩展，它允许一次计算多个标准分组的小计。grouping sets 的功能相当于将多个 group by 子句组合到一个 group by 子句中，类似于用 union all 合并多个 group by 的结果集，所以它的计算结果与排序字段的顺序无关，而且不会合并重复组。
例如group by grouping sets(rollup(a))和group by rollup(a)的结果集相同，group by grouping sets(a,b)和group by a加group by b的结果集相同。示例如下：
with t as(
select t.code,t.name,datediff(year,t.birthday,getdate()) age,t.classid,t.gender
from t_students t
where t.classid in(1,2)
)
select t.classid,count(1) 学生个数,max(t.age) 最大年龄,min(t.age) 最小年龄
from t
group by grouping sets(rollup(t.classid),cube(t.classid));
查询结果如下：
classid 学生个数 最大年龄 最小年龄 ----------- ----------- ----------- ----------- 1 10 20 15 2 9 20 16 null 19 20 15 1 10 20 15 2 9 20 16 null 19 20 15
grouping sets 中还支持group by ()，用于指定生成总计的空组。例如要查询统计浙江地区各级别行政区个数，及总计个数，示例如下：
select t.level 级别,count(1) 个数 from t_districts t where substring(t.code,1,2) = '33' group by grouping sets(t.level,());
查询结果如下：
级别 个数 ----------- ----------- 1 1 2 6 3 10 null 17
4、group by 扩展函数
4.1、grouping 函数
grouping 函数用于指示当前行是否为聚合行，如果它返回 1 则表示聚合，相反，返回 0 则表示未聚合。仅当指定了 group by 时，grouping 才能在 select 子句、having 或 order by 子句中使用。
通常将一个分组字段作为该函数的参数，然后通过判断它的返回值来区分聚集行与常规行，从而进一步对结果集美化或过滤。示例如下：
select grouping(t.level) 标志,t.level 级别,count(1) 个数 from t_districts t where substring(t.code,1,2) = '33' group by grouping sets(t.level,());
查询结果如下：
标志 级别 个数 ---- ----------- ----------- 0 1 1 0 2 6 0 3 10 1 null 17
4.2、grouping_id 函数
grouping 函数用于计算分组级别，它将返回与行相关联的 grouping 位向量对应的数值。grouping_id 按从左到右的顺序计算，如果是分组字段，则为 0，如果是小计或合计则为 1，然后按字段的顺序将计算结果组成二进制序列（位向量），最后将位向量转化为十进制数。仅当指定了 group by 时，grouping 才能在 select 子句、having 或 order by 子句中使用。
grouping_id 函数在功能上等效于多个 grouping 函数，当查询结果有多个聚合级别时，使用该函数会更容易表达行过滤条件。示例如下：
select t.courseid,t.studentid,t.counts,max(t.scores) scores,
grouping_id(t.courseid) gc,grouping_id(t.studentid) gs,grouping_id(t.counts) gt,
grouping_id(t.courseid,t.studentid) gcs,
grouping_id(t.studentid,t.counts) gst,
grouping_id(t.courseid,t.counts) gct,
grouping_id(t.courseid,t.studentid,t.counts) gcst
from t_examresults t
where t.studentid = 1 and t.counts = 2
group by rollup(t.courseid,t.studentid,t.counts);
查询结果如下：
courseid studentid counts scores gc gs gt gcs gst gct gcst ---------- ---------- --------- --------- ------- ------- ------- ------- ------- ------- ------- 1 1 2 63.0 0 0 0 0 0 0 0 1 1 null 63.0 0 0 1 0 1 1 1 1 null null 63.0 0 1 1 1 3 1 3 2 1 2 98.0 0 0 0 0 0 0 0 2 1 null 98.0 0 0 1 0 1 1 1 2 null null 98.0 0 1 1 1 3 1 3 3 1 2 73.0 0 0 0 0 0 0 0 3 1 null 73.0 0 0 1 0 1 1 1 3 null null 73.0 0 1 1 1 3 1 3 null null null 98.0 1 1 1 3 3 3 7
5、本文小结
本文主要讲述了 sql server 中分组查询的相关知识点，包括 group by 与聚合函数、having 联合使用及 group by 的标准分组、扩展分组、扩展函数等。
另外，不知道会不会有读者感到疑惑，为什么扩展分组返回的结果集中经常会出现 null 值？其实这是 null 的一个特殊应用，它在 rollup、cube 或 grouping sets 操作的结果集内作为字段的占位符，表示全体（数据）。
本文参考链接：
- 1、sql server 2016 group by
- 2、sql server 2016 having
- 3、sql server 2016 grouping
- 4、sql server 2016 grouping_id
去导航目录篇下载创建本系列博文通用库表及数据的 sql 语句
本文链接：
版权声明：本文为博客园博主 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！个人博客，能力有限，若有不当之处，敬请批评指正，谢谢！