分组查询

目录

前言

一、简单分组查询

二、CUBE 和 ROLLUP的应用

1、CUBE

2、ROLLUP

---

前言

使用聚合函数返回的是所有行数据的统计结果，如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用 GROUP  BY 子句。分组技术就是指，使用 GROUP  BY子句完成分组操作的技术。

--  语法结构

GROUP  BY  { [ ALL ] group_by_expression  [, ...n] }

WITH  { CUBE | ROLLUP }

--  ALL：包含所有分组结果，甚至包含那些不满足 WHERE子句指定搜索条件的组和结果。如果指定了 ALL，组中不满足搜索条件的空值也将作为一个组。

--  group_by_expression：执行分组的表达式，可以是列或引用列的非聚合表达式。

--  CUBE：除了返回由 GROUP  BY子句指定的列外，还返回按组统计的行，返回的结果先按分组的第一个条件进行排列显示，再按照第二个条件排列显示，依次类推，统计行包括 GROUP  BY子句指定的列的各种组合的数据统计，更改列分组的顺序会影响再结果集内生成的行数。

--  ROLLUP：此项只返回最高层的分组列，即第一个分组列的统计数据。

本文参考表：

学生表 student

成绩表 sc

一、简单分组查询

        如果在 GROUP  BY子句中没有使用 CUBE或ROLLUP关键字，那么表示这种分组的技术就是简单分组技术。

--  注：使用 GROUP  BY子句时，选择（SELECT）列表中任何非聚合表达式内的所有列都应包含在 GROUP  BY列表中（不能使用列别名），或者说，GROUP  BY表达式必须与选择（SELECT）列表表达式完全匹配。或者可以说是，在 SELECT子句中，没有使用聚合表达式的字段列，必须作为 GROUP  BY的分组表达式。

--  示例：查询 student表中男生和女生的人数。

select  ssex, count(ssex)  性别人数  from student

group by ssex

--  示例：查询 student表中每个专业的男生和女生的人数。

select  ssex, specialty, count(ssex) 性别人数  from  student

group  by  ssex, specialty

--  说明：此处列 ssex, specialty未使用聚合表达式，所以在 group  by子句中就必须包使用这两个字段进行分组，缺一不可。

        当完成数据结果的查询和统计后，可以使用 HAVING关键字对查询和统计的结果进行进一步的筛选。

--  示例：查询 sc表中，选修了两门及以上课程的学生学号和选课数。

select  sno, count(cno) 选修课程数  from  sc

group by  sno

having  count(cno) >= 2

        HAVING 与 WHERE子句的区别是，WHERE子句是对整表（源表）中的数据进行筛选；而，HAVING子句是对 GROUP  BY分组查询后产生的组再进行条件筛选。HAVING中条件一般都直接使用聚合函数，WHERE中条件不能直接使用聚合函数。

二、CUBE 和 ROLLUP的应用

1、CUBE

        CUBE指定在结果集内不仅包含由 GROUP  BY提供的行，还包含汇总行。GROUP  BY汇总行针对每个可能的组和子组组合再结果集内返回，GROUP  BY 汇总行在结果中显示为 NULL，但用来表示所有值。使用 GROUPING 函数可确定结果集内的空值是否为 GROUP  BY汇总值。

        结果集内的汇总行数取决于 GROUP  BY子句内包含的列数。GROUP  BY子句中的每个操作数（列）绑定在分组 NULL下，并且分组适用于所有其它操作数（列）。由于 CUBE返回每个可能的组和子组组合，因此不论在列分组时指定使用什么顺序，行数都相同。

--  示例：在 sc表中，求被选修的各门课程的平均成绩和选修该课程的人数，及所有课程的总平局成绩。

select  cno, avg(score) 平均成绩, count(cno) 选修人数  from  sc

group  by  cno

with  cube

--  示例：在 student表中，统计各专业男生，女生人数及每个专业的学生人数和男生总人数、女生人数以及所有学生总人数。

select  specialty, ssex,count(*) 人数  from  student

group  by  specialty,ssex

with  cube

2、ROLLUP

        ROLLUP指定在结果集内不仅包含由 GROUP  BY 提供的行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总。组的层次结构取决于列分组时指定使用的顺序。更改列分组的顺序会影响在结果集内生成的行数。

        使用 CUBE 或 ROLLUP时，不支持区分性聚合函数，如 AVG(DISTINCT  列名)、COUNT(DISTINCT  列名) 等。

--  示例：统计每个专业的男女生人数，每个专业的总人数和所有学生总人数。

select  specialty, ssex, count(*) 人数  from  student

group  by  specialty, ssex

with  rollup

        ROLLUP 是对 GROUP  BY子句的分组进行汇总 和对总数居进行汇总。

• CUBE 生成的结果集显示了所选列中值的所有组合的汇总。
• ROLLUP 生成的结果集显示了所选列中值的某一层次结构的汇总。汇总的数据排列在每个层次结构的后面，总的汇总在最后。