SQL Server温故系列(5)：SQL 查询之分组查询 GROUP BY (2)

查询结果如下：

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 函数