SQL 对表进行分组（GROUP BY）

• 一、GROUP BY 子句
• 二、聚合键中包含 NULL 的情况
• 三、使用 WHERE 子句时 GROUP BY 的执行结果
• 四、与聚合函数和 GROUP BY 子句有关的常见错误
• 请参阅

学习重点

• 使用 GROUP BY 子句可以像切蛋糕那样将表分割。通过使用聚合函数和 GROUP BY 子句，可以根据“商品种类”或者“登记日期”等将表分割后再进行汇总。

• 聚合键中包含 NULL 时，在结果中会以“不确定”行（空行）的形式表现出来。

• 使用聚合函数和 GROUP BY 子句时需要注意以下 4 点。

  ① 只能写在 SELECT 子句之中

  ② GROUP BY 子句中不能使用 SELECT 子句中列的别名

  ③ GROUP BY 子句的聚合结果是无序的

  ④ WHERE 子句中不能使用聚合函数

一、GROUP BY 子句

目前为止，我们看到的聚合函数的使用方法，无论是否包含 NULL，无论是否删除重复数据，都是针对表中的所有数据进行的汇总处理。下面，我们先把表分成几组，然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。

这里我们将要第一次接触到 GROUP BY 子句，其语法结构如下所示。

KEYWORD

• GROUP BY 子句

语法 1　使用 GROUP BY 子句进行汇总

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

下面我们就按照商品种类来统计一下数据行数（= 商品数量）（代码清单 13）。

代码清单 13　按照商品种类统计数据行数

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

执行结果

 product_type | count
--------------+------
 衣服         |     2
 办公用品     |     2
 厨房用具     |     4

如上所示，未使用 GROUP BY 子句时，结果只有 1 行，而这次的结果却是多行。这是因为不使用 GROUP BY 子句时，是将表中的所有数据作为一组来对待的。而使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。如图 4 所示，GROUP BY 子句对表进行了切分。

这样，GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式，所以是非常重要的列。当然，GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。

KEYWORD

• 聚合键

• 分组列

如果用画线的方式来切分表中数据的话，就会得到图 5 那样以商品种类为界线的三组数据。然后再计算每种商品的数据行数，就能得到相应的结果了。

法则 6

GROUP BY 就像是切分表的一把刀。

此外，GROUP BY 子句的书写位置也有严格要求，一定要写在 FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。如果无视子句的书写顺序，SQL 就一定会无法正常执行而出错。目前 SQL 的子句还没有全部登场，已经出现的各子句的暂定顺序如下所示。

? 子句的书写顺序（暂定）

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

法则 7

SQL 子句的顺序不能改变，也不能互相替换。

二、聚合键中包含 NULL 的情况

接下来我们将进货单价（purchase_price）作为聚合键对表进行切分。在 GROUP BY 子句中指定进货单价的结果请参见代码清单 14。

代码清单 14　按照进货单价统计数据行数

SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

上述 SELECT 语句的结果如下所示。

执行结果

像 790 日元或者 500 日元这样进货单价很清楚的数据行不会有什么问题，结果与之前的情况相同。问题是结果中的第一行，也就是进货单价为 NULL 的组。从结果我们可以看出，当聚合键中包含 NULL 时，也会将 NULL 作为一组特定的数据，如图 6 所示。

这里的 NULL，大家可以理解为“不确定”。

法则 8

聚合键中包含 NULL 时，在结果中会以“不确定”行（空行）的形式表现出来。

三、使用 WHERE 子句时 GROUP BY 的执行结果

在使用了 GROUP BY 子句的 SELECT 语句中，也可以正常使用 WHERE 子句。子句的排列顺序如前所述，语法结果如下所示。

语法 2　使用 WHERE 子句和 GROUP BY 子句进行汇总处理

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 WHERE
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

像这样使用 WHERE 子句进行汇总处理时，会先根据 WHERE 子句指定的条件进行过滤，然后再进行汇总处理。请看代码清单 15。

代码清单 15　同时使用 WHERE 子句和 GROUP BY 子句

SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

因为上述 SELECT 语句首先使用了 WHERE 子句对记录进行过滤，所以实际上作为聚合对象的记录只有 2 行，如表 1 所示。

表 1　WHERE 子句过滤的结果

使用进货单价对这 2 条记录进行分组，就得到了如下的执行结果。

执行结果

purchase_price  | count
----------------+------
            500 |     1
           2800 |     1

GROUP BY 和 WHERE 并用时，SELECT 语句的执行顺序如下所示。

? GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

这与之前语法 2 中的说明顺序有些不同，这是由于在 SQL 语句中，书写顺序和 DBMS 内部的执行顺序并不相同。这也是 SQL 难以理解的原因之一。

四、与聚合函数和 GROUP BY 子句有关的常见错误

截至目前，我们已经学习了 聚合函数 和 GROUP BY 子句的基本使用方法。虽然由于使用方便而经常被使用，但是书写 SQL 时却很容易出错，希望大家特别小心。

• 常见错误 ① ——在 SELECT 子句中书写了多余的列

  在使用 COUNT 这样的聚合函数时，SELECT 子句中的元素有严格的限制。实际上，使用聚合函数时，SELECT 子句中只能存在以下三种元素。

  • 常数

  • 聚合函数

  • GROUP BY 子句中指定的列名（也就是聚合键）

  在 SQL 概要 中我们介绍过，常数就是像数字 123，或者字符串 '测试' 这样写在 SQL 语句中的固定值，将常数直接写在 SELECT 子句中没有任何问题。此外还可以书写聚合函数或者聚合键，这些在之前的示例代码中都已经出现过了。

  这里经常会出现的错误就是把聚合键之外的列名书写在 SELECT 子句之中。例如代码清单 16 中的 SELECT 语句就会发生错误，无法正常执行。

  代码清单 16　在 SELECT 子句中书写聚合键之外的列名会发生错误

  SELECT product_name, purchase_price, COUNT(*)
  FROM Product
  GROUP BY purchase_price;
  

  执行结果（使用 PostgreSQL 的情况）

  ERROR：列"product,product_name"必须包含在GROUP BY子句之中，或者必须在聚合函数内使用
  行 1: SELECT product_name, purchase_price, COUNT(*)
  

  列名 product_name 并没有包含在 GROUP BY 子句当中。因此，该列名也不能书写在 SELECT 子句之中 ^[1]。

  不支持这种语法的原因，大家仔细想一想应该就明白了。通过某个聚合键将表分组之后，结果中的一行数据就代表一组。例如，使用进货单价将表进行分组之后，一行就代表了一个进货单价。问题就出在这里，聚合键和商品名并不一定是一对一的。

  例如，进货单价是 2800 日元的商品有“运动 T 恤”和“菜刀”两种，但是 2800 日元这一行应该对应哪个商品名呢（图 7）？如果规定了哪种商品优先表示的话则另当别论，但其实并没有这样的规则。

  像这样与聚合键相对应的、同时存在多个值的列出现在 SELECT 子句中的情况，理论上是不可能的。

  法则 9

  使用 GROUP BY 子句时，SELECT 子句中不能出现聚合键之外的列名。

• 常见错误 ② ——在 GROUP BY 子句中写了列的别名

  这也是一个非常常见的错误。在 SELECT 语句基础 中我们学过，SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是，在 GROUP BY 子句中是不能使用别名的。代码清单 17 中的 SELECT 语句会发生错误 ^[2]。

  代码清单 17　GROUP BY 子句中使用列的别名会引发错误

  上述语句发生错误的原因之前已经介绍过了，是 SQL 语句在 DBMS 内部的执行顺序造成的——SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并不知道。

  使用本教程提供的 PostgreSQL 执行上述 SQL 语句并不会发生错误，而会得到如下结果。但是这样的写法在其他 DBMS 中并不是通用的，因此请大家不要使用。

  执行结果（使用 PostgreSQL 的情况）

      pt     | count
  -------------+------
  衣服         |     2
  办公用品     |     2
  厨房用具     |     4
  
  

  法则 10

  在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名。

• 常见错误 ③ —— GROUP BY 子句的结果能排序吗

  GROUP BY 子句的结果通常都包含多行，有时可能还会是成百上千行。那么，这些结果究竟是按照什么顺序排列的呢？

  答案是：“随机的。”

  我们完全不知道结果记录是按照什么规则进行排序的。可能乍一看是按照行数的降序或者聚合键的升序进行排列的，但其实这些全都是偶然的。当你再次执行同样的 SELECT 语句时，得到的结果可能会按照完全不同的顺序进行排列。

  通常 SELECT 语句的执行结果的显示顺序都是随机的，因此想要按照某种特定顺序进行排序的话，需要在 SELECT 语句中进行指定。具体的方法将在 对查询结果进行排序 中学习。

  KEYWORD

  • 排序

  法则 11

  GROUP BY 子句结果的显示是无序的。

• 常见错误 ④ ——在 WHERE 子句中使用聚合函数

  最后要介绍的是初学者非常容易犯的一个错误。我们还是先来看一下之前提到的按照商品种类（product_type 列）对表进行分组，计算每种商品数据行数的例子吧。SELECT 语句如代码清单 18 所示。

  代码清单 18　按照商品种类统计数据行数

  SELECT product_type, COUNT(*)
  FROM Product
  GROUP BY product_type;
  

  执行结果

  product_type | count
  --------------+-------
  衣服         |     2
  办公用品     |     2
  厨房用具     |     4
  

  如果我们想要取出恰好包含 2 行数据的组该怎么办呢？满足要求的是“办公用品”和“衣服”。

  想要指定选择条件时就要用到 WHERE 子句，初学者通常会想到使用代码清单 19 中的 SELECT 语句吧。

  代码清单 19　在 WHERE 子句中使用聚合函数会引发错误

  SELECT product_type, COUNT(*)
  FROM Product
  WHERE COUNT(*) = 2
  GROUP BY product_type;
  

  遗憾的是，这样的 SELECT 语句在执行时会发生错误。

  执行结果（使用 PostgreSQL 的情况）

  ERROR: 不能在WHERE子句中使用聚合
  行 3: WHERE COUNT(*) = 2
  

  实际上，只有 SELECT 子句和 HAVING 子句（以及之后将要学到的 ORDER BY 子句）中能够使用 COUNT 等聚合函数。并且，HAVING 子句可以非常方便地实现上述要求。为聚合结果指定条件 我们将会学习 HAVING 子句。

  法则 12

  只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能够使用聚合函数。

专栏

DISTINCT 和 GROUP BY

细心的读者可能会发现，对表进行聚合查询 中介绍的 DISTINCT 和本文介绍的 GROUP BY 子句，都能够删除后续列中的重复数据。例如，代码清单 A 中的 2 条 SELECT 语句会返回相同的结果。

代码清单 A　DISTINCT 和 GROUP BY 能够实现相同的功能

SELECT DISTINCT product_type
 FROM Product;

SELECT product_type
 FROM Product
GROUP BY product_type;

执行结果

product_type
--------------
衣服
办公用品
厨房用具

除此之外，它们还都会把 NULL 作为一个独立的结果返回，对多列使用时也会得到完全相同的结果。其实不仅处理结果相同，执行速度也基本上差不多 ^[3]，那么到底应该使用哪一个呢？

但其实这个问题本身就是本末倒置的，我们应该考虑的是该 SELECT 语句是否满足需求。选择的标准其实非常简单，在“想要删除选择结果中的重复记录”时使用 DISTINCT，在“想要计算汇总结果”时使用 GROUP BY。

不使用 COUNT 等聚合函数，而只使用 GROUP BY 子句的 SELECT 语句，会让人觉得非常奇怪，使人产生“到底为什么要对表进行分组呢？这样做有必要吗？”等疑问。

SQL 语句的语法与英语十分相似，理解起来非常容易，如果大家浪费了这一优势，编写出一些难以理解的 SQL 语句，那就太可惜了。

请参阅

• 对表进行聚合查询
• 对表进行分组
• 为聚合结果指定条件
• 对查询结果进行排序

（完）