sql语句聚合查询_sql先排序后分组

随着表中记录（数据行）的不断积累，存储数据逐渐增加，有时我们可能希望计算出这些数据的合计值或者平均值等。

本文介绍如何使用 SQL 语句对表进行聚合和分组的方法。此外，还介绍在汇总操作时指定条件，以及对汇总结果进行升序、降序的排序方法。

一、对表进行聚合查询

本节重点

• 使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。

• 通常，聚合函数会对 NULL 以外的对象进行汇总。但是只有 COUNT 函数例外，使用 COUNT(*) 可以查出包含 NULL 在内的全部数据的行数。

• 使用 DISTINCT 关键字删除重复值。

1.1 聚合函数

通过 SQL 对数据进行某种操作或计算时需要使用函数。例如，计算表中全部数据的行数时，可以使用 COUNT 函数。该函数就是使用 COUNT（计数）来命名的。

除此之外，SQL 中还有很多其他用于汇总的函数，请大家先记住以下 5 个常用的函数。

• COUNT：计算表中的记录数（行数）

• SUM：计算表中数值列中数据的合计值

• AVG：计算表中数值列中数据的平均值

• MAX：求出表中任意列中数据的最大值

• MIN：求出表中任意列中数据的最小值

如上所示，用于汇总的函数称为聚合函数或者聚集函数，本文中统称为聚合函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行。

接下来，本文将继续使用在 SQL 如何对表进行创建、更新和删除操作 中创建的 Product 表（图 1）来学习聚合函数的使用方法。

1.2. 计算表中数据的行数

首先，我们以 COUNT 函数为例让大家对函数形成一个初步印象。函数这个词，与我们在学校数学课上学到的意思是一样的，就像是输入某个值就能输出相应结果的盒子一样 ^[1]。

使用 COUNT 函数时，输入表的列，就能够输出数据行数。如图 2 所示，将表中的列放入名称为 COUNT 的盒子中，咔嗒咔嗒地进行计算，咕咚一下行数就出来了……就像自动售货机那样，很容易理解吧。

接下来让我们看一下 SQL 中的具体书写方法。COUNT 函数的语法本身非常简单，像代码清单 1 那样写在 SELECT 子句中就可以得到表中全部数据的行数了。

代码清单 1 计算全部数据的行数

执行结果：

COUNT() 中的星号，我们在 SQL SELECT WHERE 语句如何指定一个或多个查询条件 中已经介绍过，代表全部列的意思。COUNT 函数的输入值就记述在其后的括号中。

此处的输入值称为参数或者 parameter，输出值称为返回值。这些称谓不仅本文中会使用，在多数编程语言中使用函数时都会频繁出现，请大家牢记。

1.3 计算 NULL 之外的数据的行数

想要计算表中全部数据的行数时，可以像 SELECT COUNT(*) 这样使用星号。

如果想得到 purchase_price 列（进货单价）中非空行数的话，可以像代码清单 2 那样，通过将对象列设定为参数来实现。

代码清单 2 计算 NULL 之外的数据行数

SELECT COUNT(purchase_price)
  FROM Product;

执行结果：

count
-------
    6

此时，如图 1 所示，purchase_price 列中有两行数据是 NULL，因此并不应该计算这两行。对于 COUNT 函数来说，参数列不同计算的结果也会发生变化，这一点请大家特别注意。

为了有助于大家理解，请看如下这个只包含 NULL 的表的极端例子。

我们来看一下针对上述表，将星号（*）和列名作为参数传递给 COUNT 函数时所得到的结果（代码清单 3）。

代码清单 3 将包含 NULL 的列作为参数时，COUNT(*) 和 COUNT(<列名>) 的结果并不相同

SELECT COUNT(*), COUNT(col_1)
  FROM NullTbl;

执行结果：

如上所示，即使对同一个表使用 COUNT 函数，输入的参数不同得到的结果也会不同。由于将列名作为参数时会得到 NULL 之外的数据行数，所以得到的结果是 0 行。

该特性是 COUNT 函数所特有的，其他函数并不能将星号作为参数（如果使用星号会出错）。

法则 1

COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT(<列名>) 会得到 NULL 之外的数据行数。

1.4 计算合计值

接下来我们学习其他 4 个聚合函数的使用方法。这些函数的语法基本上与 COUNT 函数相同，但就像我们此前所说的那样，在这些函数中不能使用星号作为参数。

首先，我们使用计算合计值的 SUM 函数，求出销售单价的合计值（代码清单 4）。

代码清单 4 计算销售单价的合计值

SELECT SUM(sale_price)
  FROM Product;

执行结果：

  sum
------
 16780

得到的结果 16780 元，是所有销售单价（sale_price 列）的合计，与下述计算式的结果相同。

接下来，我们将销售单价和进货单价（purchase_price 列）的合计值一起计算出来（代码清单 5）。

代码清单 5 计算销售单价和进货单价的合计值

SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;

执行结果：

这次我们通过 SUM(purchase_price) 将进货单价的合计值也一起计算出来了，但有一点需要大家注意。具体的计算过程如下所示。

大家都已经注意到了吧，与销售单价不同，进货单价中有两条不明数据 NULL。对于 SUM 函数来说，即使包含 NULL，也可以计算出合计值。

还记得 SQL SELECT WHERE 语句如何指定一个或多个查询条件 内容的读者可能会产生如下疑问。

“四则运算中如果存在 NULL，结果一定是 NULL，那此时进货单价的合计值会不会也是 NULL 呢？”

有这样疑问的读者思维很敏锐，但实际上这两者并不矛盾。从结果上说，所有的聚合函数，如果以列名为参数，那么在计算之前就已经把 NULL 排除在外了。

因此，无论有多少个 NULL 都会被无视。这与“等价为 0”并不相同 ^[2]。

因此，上述进货单价的计算表达式，实际上应该如下所示。

法则 2

聚合函数会将 NULL 排除在外。但 COUNT(*) 例外，并不会排除 NULL。

1.5 计算平均值

接下来，我们练习一下计算多行数据的平均值。为此，我们需要使用 AVG 函数，其语法和 SUM 函数完全相同（代码清单 6）。

代码清单 6 计算销售单价的平均值

SELECT AVG(sale_price)
  FROM Product;

执行结果：

         avg
----------------------
2097.5000000000000000

平均值的计算式如下所示。

(值的合计)/(值的个数) 就是平均值的计算公式了。下面我们也像使用 SUM 函数那样，计算一下包含 NULL 的进货单价的平均值（代码清单 7）。

代码清单 7 计算销售单价和进货单价的平均值

SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

执行结果：

计算进货单价平均值的情况与 SUM 函数相同，会事先删除 NULL 再进行计算，因此计算式如下所示。

需要注意的是分母是 6 而不是 8，减少的两个也就是那两条 NULL 的数据。

但是有时也想将 NULL 作为 0 进行计算，具体的实现方式请参考 SQL 常用的函数。

1.6 计算最大值和最小值

想要计算出多条记录中的最大值或最小值，可以分别使用 MAX 和 MIN 函数，它们是英语 maximam（最大值）和 minimum（最小值）的缩写，很容易记住。

这两个函数的语法与 SUM 的语法相同，使用时需要将列作为参数（代码清单 8）。

代码清单 8 计算销售单价的最大值和进货单价的最小值

SELECT MAX(sale_price), MIN(purchase_price)
  FROM Product;

执行结果：

如图 1 所示，我们取得了相应的最大值和最小值。

但是，MAX/MIN 函数和 SUM/AVG 函数有一点不同，那就是 SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

例如，对图 1 中日期类型的列 regist_date 使用 MAX/MIN 函数进行计算的结果如下所示（代码清单 9）。

代码清单 9 计算登记日期的最大值和最小值

SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;

执行结果：

刚刚我们说过 MAX/MIN 函数适用于任何数据类型的列，也就是说，只要是能够排序的数据，就肯定有最大值和最小值，也就能够使用这两个函数。

对日期来说，平均值和合计值并没有什么实际意义，因此不能使用 SUM/AVG 函数。

这点对于字符串类型的数据也适用，字符串类型的数据能够使用 MAX/MIN 函数，但不能使用 SUM/AVG 函数。

法则 3

MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。

1.7 使用聚合函数删除重复值（关键字 DISTINCT）

接下来我们考虑一下下面这种情况。

在图 1 中我们可以看到，商品种类（product_type 列）和销售单价（sale_price 列）的数据中，存在多行数据相同的情况。

例如，拿商品种类来说，表中总共有 3 种商品共 8 行数据，其中衣服 2 行，办公用品 2 行，厨房用具 4 行。

如果想要计算出商品种类的个数，怎么做比较好呢？删除重复数据然后再计算数据行数似乎是个不错的办法。

实际上，在使用 COUNT 函数时，将 SQL 如何对表进行创建、更新和删除操作 中介绍过的 DISTINCT 关键字作为参数，就能得到我们想要的结果了（代码清单 10）。

代码清单 10 计算去除重复数据后的数据行数

SELECT COUNT(DISTINCT product_type)
  FROM Product;

执行结果：

 count
-------
     3

请注意，这时 DISTINCT 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。

如果像代码清单 11 那样写在括号外的话，就会先计算出数据行数，然后再删除重复数据，结果就得到了 product_type 列的所有行数（也就是 8）。

代码清单 11 先计算数据行数再删除重复数据的结果

SELECT DISTINCT COUNT(product_type)
  FROM Product;

执行结果：

 count
-------
     8

法则 4

想要计算值的种类时，可以在 COUNT 函数的参数中使用 DISTINCT。

不仅限于 COUNT 函数，所有的聚合函数都可以使用 DISTINCT。

下面我们来看一下使用 DISTINCT 和不使用 DISTINCT 时 SUM 函数的执行结果（代码清单 12）。

代码清单 12 使不使用 DISTINCT 时的动作差异（SUM 函数）

SELECT SUM(sale_price), SUM(DISTINCT sale_price)
  FROM Product;

执行结果：

左侧是未使用 DISTINCT 时的合计值，和我们之前计算的结果相同，都是 16780 元。

右侧是使用 DISTINCT 后的合计值，比之前的结果少了 500 元。

这是因为表中销售单价为 500 元的商品有两种——“打孔器”和“叉子”，在删除重复数据之后，计算对象就只剩下一条记录了。

法则 5

在聚合函数的参数中使用 DISTINCT，可以删除重复数据。

二、对表进行分组

本节重点

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

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

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

1. 只能写在 SELECT 子句之中

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

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

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

2.1 GROUP BY 子句

目前为止，我们看到的聚合函数的使用方法，无论是否包含 NULL，无论是否删除重复数据，都是针对表中的所有数据进行的汇总处理。

下面，我们先把表分成几组，然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。

这里我们将要第一次接触到 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 子句一样，可以通过逗号分隔指定多列。

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

法则 6

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

此外，GROUP BY 子句的书写位置也有严格要求，一定要写在 FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。

如果无视子句的书写顺序，SQL 就一定会无法正常执行而出错。目前 SQL 的子句还没有全部登场，已经出现的各子句的暂定顺序如下所示。

子句的书写顺序（暂定）

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

法则 7

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

2.2 聚合键中包含 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 时，在结果中会以“不确定”行（空行）的形式表现出来。

2.3 使用 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 难以理解的原因之一。

2.4 与聚合函数和 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 子句之中 ^[3]。

  不支持这种语法的原因，大家仔细想一想应该就明白了。通过某个聚合键将表分组之后，结果中的一行数据就代表一组。

  例如，使用进货单价将表进行分组之后，一行就代表了一个进货单价。问题就出在这里，聚合键和商品名并不一定是一对一的。

  例如，进货单价是 2800 元的商品有“运动 T 恤”和“菜刀”两种，但是 2800 元这一行应该对应哪个商品名呢（图 7）？

  如果规定了哪种商品优先表示的话则另当别论，但其实并没有这样的规则。

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

法则 9

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

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

  这也是一个非常常见的错误。在 为列设定别名 中我们学过，SELECT 子句中的项目可以通过 AS 关键字来指定别名。

  但是，在 GROUP BY 子句中是不能使用别名的。代码清单 17 中的 SELECT 语句会发生错误 ^[4]。

  代码清单 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 语句中进行指定。具体的方法将在本文第 4 节中学习。

  法则 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

细心的读者可能会发现，第 1 节中介绍的 DISTINCT 和第 2 节介绍的 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 作为一个独立的结果返回，对多列使用时也会得到完全相同的结果。其实不仅处理结果相同，执行速度也基本上差不多，那么到底应该使用哪一个呢？

但其实这个问题本身就是本末倒置的，我们应该考虑的是该 SELECT 语句是否满足需求。

选择的标准其实非常简单，在“想要删除选择结果中的重复记录”时使用 DISTINCT，在“想要计算汇总结果”时使用 GROUP BY。

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

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

三、为聚合结果指定条件

本节重点

使用 COUNT 函数等对表中数据进行汇总操作时，为其指定条件的不是 WHERE 子句，而是 HAVING 子句。

聚合函数可以在 SELECT 子句、HAVING 子句和 ORDER BY 子句中使用。

HAVING 子句要写在 GROUP BY 子句之后。

WHERE 子句用来指定数据行的条件，HAVING 子句用来指定分组的条件。

3.1 HAVING 子句

使用前一节学过的 GROUP BY 子句，可以得到将表分组后的结果。在此，我们来思考一下通过指定条件来选取特定组的方法。

例如，如何才能取出“聚合结果正好为 2 行的组”呢（图 8）？

说到指定条件，估计大家都会首先想到 WHERE 子句。但是，WHERE 子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等）。

因此，对集合指定条件就需要使用其他的子句了，此时便可以用 HAVING 子句 ^[5]。

HAVING 子句的语法如下所示。

语法 3 HAVING 子句

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

HAVING 子句必须写在 GROUP BY 子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。

使用 HAVING 子句时 SELECT 语句的顺序

SELECT → FROM → WHERE → GROUP BY → HAVING

法则 13

HAVING 子句要写在 GROUP BY 子句之后。

接下来就让我们练习一下 HAVING 子句吧。例如，针对按照商品种类进行分组后的结果，指定“包含的数据行数为 2 行”这一条件的 SELECT 语句，请参见代码清单 20。

代码清单 20 从按照商品种类进行分组后的结果中，取出“包含的数据行数为 2 行”的组

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

执行结果：

 product_type | count
--------------+------
 衣服         |     2
 办公用品     |     2

我们可以看到执行结果中并没有包含数据行数为 4 行的“厨房用具”。

未使用 HAVING 子句时的执行结果中包含“厨房用具”，但是通过设置 HAVING 子句的条件，就可以选取出只包含 2 行数据的组了（代码清单 21）。

代码清单 21 不使用 HAVING 子句的情况

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

执行结果：

下面我们再来看一个使用 HAVING 子句的例子。这次我们还是按照商品种类对表进行分组，但是条件变成了“销售单价的平均值大于等于 2500 元”。

首先来看一下不使用 HAVING 子句的情况，请参见代码清单 22。

代码清单 22 不使用 HAVING 子句的情况

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type;

执行结果：

 product_type |         avg
--------------+----------------------
 衣服         | 2500.0000000000000000
 办公用品     |  300.0000000000000000
 厨房用具     | 2795.0000000000000000

按照商品种类进行切分的 3 组数据都显示出来了。下面我们使用 HAVING 子句来设定条件，请参见代码清单 23。

代码清单 23 使用 HAVING 子句设定条件的情况

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

执行结果：

 product_type |          avg
--------------+----------------------
 衣服         | 2500.0000000000000000
 厨房用具     | 2795.0000000000000000

销售单价的平均值为 300 元的“办公用品”在结果中消失了。

3.2 HAVING 子句的构成要素

HAVING 子句和包含 GROUP BY 子句时的 SELECT 子句一样，能够使用的要素有一定的限制，限制内容也是完全相同的。HAVING 子句中能够使用的 3 种要素如下所示。

• 常数

• 聚合函数

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

代码清单 20 中的例文指定了 HAVING COUNT(*)= 2 这样的条件，其中 COUNT(*) 是聚合函数，2 是常数，全都满足上述要求。

反之，如果写成了下面这个样子就会发生错误（代码清单 24）。

代码清单 24 HAVING 子句的不正确使用方法

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_name = "圆珠笔";

执行结果：

ERROR: 列"product,product_name"必须包含在GROUP BY子句当中，或者必须在聚合函数中使用
行 4: HAVING product_name = "圆珠笔";

product_name 列并不包含在 GROUP BY 子句之中，因此不允许写在 HAVING 子句里。

在思考 HAVING 子句的使用方法时，把一次汇总后的结果（类似表 2 的表）作为 HAVING 子句起始点的话更容易理解。

表 2 按照商品种类分组后的结果

可以把这种情况想象为使用 GROUP BY 子句时的 SELECT 子句。汇总之后得到的表中并不存在 product_name 这个列，SQL 当然无法为表中不存在的列设定条件了。

3.3 相对于 HAVING 子句，更适合写在 WHERE 子句中的条件

也许有的读者已经发现了，有些条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中。这些条件就是聚合键所对应的条件。

原表中作为聚合键的列也可以在 HAVING 子句中使用。因此，代码清单 25 中的 SELECT 语句也是正确的。

代码清单 25 将条件书写在 HAVING 子句中的情况

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_type = "衣服";

执行结果：

 product_type | count
--------------+------
 衣服         |     2

上述 SELECT 语句的返回结果与代码清单 26 中 SELECT 语句的返回结果是相同的。

代码清单 26 将条件书写在 WHERE 子句中的情况

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

执行结果：

 product_type | count
--------------+------
 衣服         |     2

虽然条件分别写在 WHERE 子句和 HAVING 子句当中，但是条件的内容以及返回的结果都完全相同。因此，大家可能会觉得两种书写方式都没问题。

如果仅从结果来看的话，确实如此。但笔者却认为，聚合键所对应的条件还是应该书写在 WHERE 子句之中。

理由有两个。

首先，根本原因是 WHERE 子句和 HAVING 子句的作用不同。如前所述，HAVING 子句是用来指定“组”的条件的。

因此，“行”所对应的条件还是应该写在 WHERE 子句当中。这样一来，书写出的 SELECT 语句不但可以分清两者各自的功能，理解起来也更加容易。

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件

其次，对初学者来说，研究 DBMS 的内部实现这一话题有些深奥，这里就不做介绍了，感兴趣的读者可以参考随后的专栏——WHERE 子句和 HAVING 子句的执行速度。

法则 14

聚合键所对应的条件不应该书写在 HAVING 子句当中，而应该书写在 WHERE 子句当中。

专栏

WHERE 子句和 HAVING 子句的执行速度

在 WHERE 子句和 HAVING 子句中都可以使用的条件，最好写在 WHERE 子句中的另一个理由与性能即执行速度有关系。由于性能不在本文介绍的范围之内，因此暂不进行说明。

通常情况下，为了得到相同的结果，将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。

为了理解其中原因，就要从 DBMS 的内部运行机制来考虑。使用 COUNT 函数等对表中的数据进行聚合操作时，DBMS 内部就会进行排序处理。排序处理是会大大增加机器负担的高负荷的处理。

因此，只有尽可能减少排序的行数，才能提高处理速度。

通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。

但 HAVING 子句是在排序之后才对数据进行分组的，因此与在 WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。

虽然 DBMS 的内部处理不尽相同，但是对于排序处理来说，基本上都是一样的。

此外，WHERE 子句更具速度优势的另一个理由是，可以对 WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。

创建索引是一种非常普遍的提高 DBMS 性能的方法，效果也十分明显，这对 WHERE 子句来说也十分有利。

四、对查询结果进行排序

学习重点

• 使用 ORDER BY 子句对查询结果进行排序。

• 在 ORDER BY 子句中列名的后面使用关键字 ASC 可以进行升序排序，使用 DESC 关键字可以进行降序排序。

• ORDER BY 子句中可以指定多个排序键。

• 排序健中包含 NULL 时，会在开头或末尾进行汇总。

• ORDER BY 子句中可以使用 SELECT 子句中定义的列的别名。

• ORDER BY 子句中可以使用 SELECT 子句中未出现的列或者聚合函数。

• ORDER BY 子句中不能使用列的编号。

4.1 ORDER BY 子句

截至目前，我们使用了各种各样的条件对表中的数据进行查询。本节让我们再来回顾一下简单的 SELECT 语句（代码清单 27）。

代码清单 27 显示商品编号、商品名称、销售单价和进货单价的 SELECT 语句

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product;

执行结果：

 product_id | product_name  |  sale_price  |  purchase_price
------------+---------------+--------------+----------------
 0001       | T恤衫         |         1000 |             500
 0002       | 打孔器        |          500 |             320
 0003       | 运动T恤       |         4000 |            2800
 0004       | 菜刀          |         3000 |            2800
 0005       | 高压锅        |         6800 |            5000
 0006       | 叉子          |          500 |
 0007       | 擦菜板        |          880 |             790
 0008       | 圆珠笔        |          100 |

对于上述结果，在此无需特别说明，本节要为大家介绍的不是查询结果，而是查询结果的排列顺序。

那么，结果中的 8 行记录到底是按照什么顺序排列的呢？乍一看，貌似是按照商品编号从小到大的顺序（升序）排列的。

其实，排列顺序是随机的，这只是个偶然。因此，再次执行同一条 SELECT 语句时，顺序可能大为不同。

通常，从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知。即使是同一条 SELECT 语句，每次执行时排列顺序很可能发生改变。

但是不进行排序，很可能出现结果混乱的情况。这时，便需要通过在 SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序。

ORDER BY 子句的语法如下所示。

语法 4 ORDER BY 子句

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……

例如，按照销售单价由低到高，也就是升序排列时，请参见代码清单 28。

代码清单 28 按照销售单价由低到高（升序）进行排列

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price;

执行结果：

不论何种情况，ORDER BY 子句都需要写在 SELECT 语句的末尾。

这是因为对数据行进行排序的操作必须在结果即将返回时执行。ORDER BY 子句中书写的列名称为排序键。该子句与其他子句的顺序关系如下所示。

子句的书写顺序

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

法则 15

ORDER BY 子句通常写在 SELECT 语句的末尾。

不想指定数据行的排列顺序时，SELECT 语句中不写 ORDER BY 子句也没关系。

4.2 指定升序或降序

与上述示例相反，想要按照销售单价由高到低，也就是降序排列时，可以参见代码清单 29，在列名后面使用 DESC 关键字。

代码清单 29 按照销售单价由高到低（降序）进行排列

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC;

执行结果：

product_id  | product_name | sale_ price | purchase_ price
------------+--------------+-------------+----------------
 0005       | 高压锅       |        6800 |            5000
 0003       | 运动T恤      |        4000 |            2800
 0004       | 菜刀         |        3000 |            2800
 0001       | T恤衫        |        1000 |             500
 0007       | 擦菜板       |         880 |             790
 0002       | 打孔器       |         500 |             320
 0006       | 叉子         |         500 |
 0008       | 圆珠笔       |         100 |

如上所示，这次销售单价最高（6800 元）的高压锅排在了第一位。

其实，使用升序进行排列时，正式的书写方式应该是使用关键字 ASC，但是省略该关键字时会默认使用升序进行排序。

这可能是因为实际应用中按照升序排序的情况更多吧。ASC 和 DESC 是 ascendent（上升的）和 descendent（下降的）这两个单词的缩写。

法则 16

未指定 ORDER BY 子句中排列顺序时会默认使用升序进行排列。

由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序。

4.3 指定多个排序键

本节开头曾提到过对销售单价进行升序排列的 SELECT 语句（代码清单 28）的执行结果，我们再来回顾一下。

可以发现销售单价为 500 元的商品有 2 件。相同价格的商品的顺序并没有特别指定，或者可以说是随机排列的。

如果想要对该顺序的商品进行更细致的排序的话，就需要再添加一个排序键。在此，我们以添加商品编号的升序为例，请参见代码清单 30。

代码清单 30 按照销售单价和商品编号的升序进行排序

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price, product_id;

执行结果：

这样一来，就可以在 ORDER BY 子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。当然，也可以同时使用 3 个以上的排序键。

4.4 NULL 的顺序

在此前的示例中，我们已经使用过销售单价（sale_price 列）作为排序键了，这次让我们尝试使用进货单价（purchase_price 列）作为排序键吧。

此时，问题来了，圆珠笔和叉子对应的值是 NULL，究竟 NULL 会按照什么顺序进行排列呢？ NULL 是大于 100 还是小于 100 呢？或者说 5000 和 NULL 哪个更大呢？

请大家回忆一下我们在 不能对 NULL 使用比较运算符 中学过的内容。没错，不能对 NULL 使用比较运算符，也就是说，不能对 NULL 和数字进行排序，也不能与字符串和日期比较大小。

因此，使用含有 NULL 的列作为排序键时， NULL 会在结果的开头或末尾汇总显示（代码清单 31）。

代码清单 31 按照进货单价的升序进行排列

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY purchase_price;

执行结果：

究竟是在开头显示还是在末尾显示，并没有特殊规定。某些 DBMS 中可以指定 NULL 在开头或末尾显示，希望大家对自己使用的 DBMS 的功能研究一下。

法则 17

排序键中包含 NULL 时，会在开头或末尾进行汇总。

4.5 在排序键中使用显示用的别名

在第 2 节“常见错误 ②”中曾介绍过，在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名，但是在 ORDER BY 子句中却是允许使用别名的。

因此，代码清单 32 中的 SELECT 语句并不会出错，可正确执行。

代码清单 32 ORDER BY 子句中可以使用列的别名

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
  FROM Product
ORDER BY sp, id;

上述 SELECT 语句与之前按照“销售单价和商品编号的升序进行排列”的 SELECT 语句（代码清单 31）意思完全相同：

  id  | product_name  |  sp   | purchase_price
------+---------------+-------+---------------
 0008 | 圆珠笔        |   100 |
 0002 | 打孔器        |   500 |            320
 0006 | 叉子          |   500 |
 0007 | 擦菜板        |   880 |            790
 0001 | T恤衫         |  1000 |            500
 0004 | 菜刀          |  3000 |           2800
 0003 | 运动T恤       |  4000 |           2800
 0005 | 高压锅        |  6800 |           5000

不能在 GROUP BY 子句中使用的别名，为什么可以在 ORDER BY 子句中使用呢？这是因为 SQL 语句在 DBMS 内部的执行顺序被掩盖起来了。

SELECT 语句按照子句为单位的执行顺序如下所示。

使用 HAVING 子句时 SELECT 语句的顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

这只是一个粗略的总结，虽然具体的执行顺序根据 DBMS 的不同而不同，但是大家有这样一个大致的印象就可以了。

一定要记住 SELECT 子句的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。

因此，在执行 GROUP BY 子句时，SELECT 语句中定义的别名无法被识别 [^8]。对于在 SELECT 子句之后执行的 ORDER BY 子句来说，就没有这样的问题了。

[^8] 也是因为这一原因，HAVING 子句也不能使用别名。

法则 18

在 ORDER BY 子句中可以使用 SELECT 子句中定义的别名。

4.6 ORDER BY 子句中可以使用的列

ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT 子句之中的列（代码清单 33）。

代码清单 33 SELECT 子句中未包含的列也可以在 ORDER BY 子句中使用

SELECT product_name, sale_price, purchase_price
  FROM Product
ORDER BY product_id;

执行结果：

 product_name  | sale_price  | purchase_price
---------------+-------------+----------------
 T恤衫         |        1000 |            500
 打孔器        |         500 |            320
 运动T恤       |        4000 |           2800
 菜刀          |        3000 |           2800
 高压锅        |        6800 |           5000
 叉子          |         500 |
 擦菜板        |         880 |            790
 圆珠笔        |         100 |

除此之外，还可以使用聚合函数（代码清单 34）。

代码清单 34 ORDER BY 子句中也可以使用聚合函数

执行结果：

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

法则 19

在 ORDER BY 子句中可以使用 SELECT 子句中未使用的列和聚合函数。

4.7 不要使用列编号

在 ORDER BY 子句中，还可以使用在 SELECT 子句中出现的列所对应的编号，是不是没想到？

列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3,…）。因此，代码清单 35 中的两条 SELECT 语句的含义是相同的。

代码清单 35 ORDER BY 子句中可以使用列的编号

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC, product_id;

-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY 3 DESC, 1;

上述第 2 条 SELECT 语句中的 ORDER BY 子句所代表的含义，就是“按照 SELECT 子句中第 3 列的降序和第 1 列的升序进行排列”，这和第 1 条 SELECT 语句的含义完全相同。

执行结果：

product_id | product_name  | sale_price  | purchase_price
-----------+---------------+-------------+----------------
 0005      | 高压锅        |        6800 |           5000
 0003      | 运动T恤       |        4000 |           2800
 0004      | 菜刀          |        3000 |           2800
 0001      | T恤衫         |        1000 |            500
 0007      | 擦菜板        |         880 |            790
 0002      | 打孔器        |         500 |            320
 0006      | 叉子          |         500 |
 0008      | 圆珠笔        |         100 |

虽然列编号使用起来非常方便，但我们并不推荐使用，原因有以下两点。

第一，代码阅读起来比较难。使用列编号时，如果只看 ORDER BY 子句是无法知道当前是按照哪一列进行排序的，只能去 SELECT 子句的列表中按照列编号进行确认。

上述示例中 SELECT 子句的列数比较少，因此可能并没有什么明显的感觉。

但是在实际应用中往往会出现列数很多的情况，而且 SELECT 子句和 ORDER BY 子句之间，还可能包含很复杂的 WHERE 子句和 HAVING 子句，直接人工确认实在太麻烦了。

第二，这也是最根本的问题，实际上，在 SQL-92 ^[6] 中已经明确指出该排序功能将来会被删除。

因此，虽然现在使用起来没有问题，但是将来随着 DBMS 的版本升级，可能原本能够正常执行的 SQL 突然就会出错。

不光是这种单独使用的 SQL 语句，对于那些在系统中混合使用的 SQL 来说，更要极力避免。

法则 20

在 ORDER BY 子句中不要使用列编号。

原文链接：https://www.developerastrid.com/sql/sql-aggregate-group-by-having-order-by/

（完）