CppGuide社区
目录

第8章 分组和聚合

# 第8章 分组和聚合

数据通常以数据库用户所需的最低粒度级别进行存储。例如，如果会计部门的查克（Chuck）需要查看单个客户的交易记录，那么数据库中就需要有一个表来存储这些单个交易。然而，这并不意味着所有用户都必须按照数据库中的存储方式来处理数据。本章重点介绍如何对数据进行分组和聚合，以便用户能够以比数据库中存储粒度更高的级别与数据进行交互。

# 分组概念

有时，你可能想要从数据中发现一些趋势，这需要数据库服务器对数据进行一定的处理，才能生成你想要的结果。例如，假设你负责银行的运营工作，想要了解每位银行柜员新开了多少个账户。你可以发出一个简单的查询来查看原始数据：

mysql> SELECT open_emp_id
       FROM account;
+-------------+
| open_emp_id |
+-------------+
| 1           |
| 1           |
| 1           |
| 1           |
| 1           |
| 1           |
| 1           |
| 1           |
| 10          |
| 10          |
| 10          |
| 10          |
| 10          |
| 10          |
| 10          |
| 13          |
| 13          |
| 13          |
| 16          |
| 16          |
| 16          |
| 16          |
| 16          |
| 16          |
+-------------+
24 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

由于account表中只有24行数据，相对容易看出有四位不同的员工开设了账户，并且员工ID为16的柜员开了6个账户。然而，如果银行有几十名员工和数千个账户，这种方法将既繁琐又容易出错。

相反，你可以使用GROUP BY子句让数据库服务器为你对数据进行分组。下面是同样的查询，但使用了GROUP BY子句按员工ID对账户数据进行分组：

mysql> SELECT open_emp_id
       FROM account
       GROUP BY open_emp_id;
+-------------+
| open_emp_id |
+-------------+
| 1           |
| 10          |
| 13          |
| 16          |
+-------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

结果集中，open_emp_id列的每个不同值对应一行，因此结果集只有4行，而不是完整的24行。结果集变小是因为这四名员工每人都开设了多个账户。若想查看每位柜员开设的账户数量，可以在SELECT子句中使用聚合函数来统计每个组中的行数：

mysql> SELECT open_emp_id, COUNT(*) how_many
       FROM account
       GROUP BY open_emp_id;
+-------------+-----------+
| open_emp_id | how_many  |
+-------------+-----------+
| 1           | 8         |
| 10          | 7         |
| 13          | 3         |
| 16          | 6         |
+-------------+-----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

聚合函数COUNT()统计每个组中的行数，星号表示让服务器统计组中的所有内容。通过结合使用GROUP BY子句和COUNT()聚合函数，你无需查看原始数据，就能生成恰好能回答业务问题的数据。

在对数据进行分组时，你可能需要根据数据分组而不是原始数据，从结果集中过滤掉不需要的数据。由于GROUP BY子句在WHERE子句求值之后运行，因此你不能为了这个目的在WHERE子句中添加过滤条件。例如，下面尝试过滤掉员工开设账户少于5个的情况：

mysql> SELECT open_emp_id, COUNT(*) how_many
       FROM account
       WHERE COUNT(*) > 4
       GROUP BY open_emp_id;
ERROR 1111 (HY000): Invalid use of group function
1
2
3
4
5

不能在WHERE子句中引用聚合函数COUNT(*)，因为在计算WHERE子句时，分组尚未生成。相反，你必须将分组过滤条件放在HAVING子句中。使用HAVING子句的查询如下：

mysql> SELECT open_emp_id, COUNT(*) how_many
       FROM account
       GROUP BY open_emp_id
       HAVING COUNT(*) > 4;
+-------------+-----------+
| open_emp_id | how_many  |
+-------------+-----------+
| 1           | 8         |
| 10          | 7         |
| 16          | 6         |
+-------------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

由于HAVING子句过滤掉了成员少于5个的组，结果集现在只包含开设了5个或更多账户的员工，因此员工ID 13被排除在结果之外。

# 聚合函数

聚合函数对一个组中的所有行执行特定操作。虽然每个数据库服务器都有自己的一组特定聚合函数，但所有主流服务器都实现的常见聚合函数包括：

  • MAX()：返回一组值中的最大值。
  • MIN()：返回一组值中的最小值。
  • AVG()：返回一组值的平均值。
  • SUM()：返回一组值的总和。
  • COUNT()：返回一组值的数量。

下面的查询使用所有常见的聚合函数来分析所有支票账户的可用余额：

mysql> SELECT MAX(avail_balance) max_balance,
       MIN(avail_balance) min_balance,
       AVG(avail_balance) avg_balance,
       SUM(avail_balance) tot_balance,
       COUNT(*) num_accounts
       FROM account
       WHERE product_cd = 'CHK';
+-------------+-------------+------------------+-------------+--------------+
| max_balance | min_balance | avg_balance      | tot_balance | num_accounts |
+-------------+-------------+------------------+-------------+--------------+
| 38552.05    | 122.37      | 7300.800985       | 73008.01     | 10         |
+-------------+-------------+------------------+-------------+--------------+
1 row in set (0.09 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

这个查询的结果表明，在account表的10个支票账户中，最大余额为38552.05美元，最小余额为122.37美元，平均余额为7300.80美元，所有10个账户的总余额为73008.01美元。希望这能让你了解这些聚合函数的作用；接下来的小节将进一步说明如何使用这些函数。

# 隐式分组与显式分组

在前面的示例中，查询返回的每个值都是由聚合函数生成的，并且这些聚合函数应用于由过滤条件product_cd = 'CHK'指定的行组。由于没有GROUP BY子句，因此存在一个单一的隐式分组（查询返回的所有行）。

然而，在大多数情况下，你会希望在检索聚合函数生成的列的同时，检索其他列。例如，如果你想扩展前面的查询，对每种产品类型而不仅仅是支票账户执行相同的五个聚合函数操作呢？对于这个查询，你会希望检索product_cd列以及五个聚合函数，如下所示：

SELECT product_cd,
       MAX(avail_balance) max_balance,
       MIN(avail_balance) min_balance,
       AVG(avail_balance) avg_balance,
       SUM(avail_balance) tot_balance,
       COUNT(*) num_accounts
FROM account;
1
2
3
4
5
6
7

然而，如果你尝试执行这个查询，将会收到以下错误：

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
1

虽然你可能很清楚自己希望将聚合函数应用于account表中找到的每组产品，但这个查询失败了，因为你没有明确指定数据应如何分组。因此，你需要添加一个GROUP BY子句，以指定聚合函数应应用于哪一组行：

mysql> SELECT product_cd,
    ->        MAX(avail_balance) max_balance,
    ->        MIN(avail_balance) min_balance,
    ->        AVG(avail_balance) avg_balance,
    ->        SUM(avail_balance) tot_balance,
    ->        COUNT(*) num_accts
    -> FROM account
    -> GROUP BY product_cd;
+------------+-------------+-------------+--------------+-------------+-----------+
| product_cd | max_balance | min_balance | avg_balance  | tot_balance | num_accts |
+------------+-------------+-------------+--------------+-------------+-----------+
| BUS        | 9345.55     | 0.00        | 4672.774902  | 9345.55     | 2         |
| CD         | 10000.00    | 1500.00     | 4875.000000  | 19500.00    | 4         |
| CHK        | 38552.05    | 122.37      | 7300.800985  | 73008.01    | 10        |
| MM         | 9345.55     | 2212.50     | 5681.713216  | 17045.14    | 3         |
| SAV        | 767.77      | 200.00      | 463.940002   | 1855.76     | 4         |
| SBL        | 50000.00    | 50000.00    | 50000.000000 | 50000.00    | 1         |
+------------+-------------+-------------+--------------+-------------+-----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

通过包含GROUP BY子句，服务器知道首先将product_cd列中具有相同值的行分组在一起，然后对这六个组中的每一组应用五个聚合函数。

# 统计不同值的数量

当使用COUNT()函数确定每个组中的成员数量时，你可以选择统计组中的所有成员，或者仅统计组中所有成员某一列的不同值。例如，考虑以下数据，这些数据显示了负责开设每个账户的员工：

mysql> SELECT account_id, open_emp_id
       FROM account
       ORDER BY open_emp_id;
+------------+-------------+
| account_id | open_emp_id |
+------------+-------------+
| 8          | 1           |
| 9          | 1           |
| 10         | 1           |
| 12         | 1           |
| 13         | 1           |
| 17         | 1           |
| 18         | 1           |
| 19         | 1           |
| 2          | 10          |
| 3          | 10          |
| 4          | 10          |
| 5          | 10          |
| 14         | 10          |
| 22         | 10          |
| 6          | 13          |
| 7          | 13          |
| 24         | 13          |
| 11         | 16          |
| 15         | 16          |
| 16         | 16          |
| 20         | 16          |
| 21         | 16          |
| 23         | 16          |
+------------+-------------+
24 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

可以看到，四个不同的员工（员工ID为1、10、13和16）开设了多个账户。假设你不想手动统计，而是希望创建一个查询来统计开设账户的员工数量。如果你将COUNT()函数应用于open_emp_id列，将会看到以下结果：

mysql> SELECT COUNT(open_emp_id)
       FROM account;
+--------------------+
| COUNT(open_emp_id) |
+--------------------+
| 24                 |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

在这种情况下，指定open_emp_id列作为要统计的列，其结果与指定COUNT(*)相同。如果你想统计组中的不同值，而不仅仅是统计组中的行数，则需要指定DISTINCT关键字，如下所示：

mysql> SELECT COUNT(DISTINCT open_emp_id)
       FROM account;
+-----------------------------+
| COUNT(DISTINCT open_emp_id) |
+-----------------------------+
| 4                           |
+-----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

因此，通过指定DISTINCT，COUNT()函数会检查组中每个成员某一列的值，以查找并删除重复项，而不是简单地统计组中的值的数量。

# 使用表达式

除了使用列作为聚合函数的参数之外，你还可以构建表达式作为参数。例如，你可能想要找出所有账户中未结算存款的最大值，这可以通过未结算余额减去可用余额来计算。你可以通过以下查询实现：

mysql> SELECT MAX(pending_balance - avail_balance) max_uncleared
       FROM account;
+---------------+
| max_uncleared |
+---------------+
| 660.00        |
+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

虽然这个例子使用了一个相当简单的表达式，但只要返回数字、字符串或日期，用作聚合函数参数的表达式可以根据需要变得很复杂。在第11章中，我将向你展示如何将CASE表达式与聚合函数一起使用，以确定特定行是否应包含在聚合中。

# 空值的处理方式

在进行聚合操作，或者实际上任何类型的数值计算时，你都应当始终考虑空值（null values）可能会对计算结果产生何种影响。为了加以说明，我将创建一个简单的表来存储数值数据，并使用集合{1, 3, 5}填充它：

mysql> CREATE TABLE number_tbl
    -> (val SMALLINT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO number_tbl VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO number_tbl VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO number_tbl VALUES (5);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

考虑以下查询，它对这组数字执行五个聚合函数：

mysql> SELECT COUNT(*) num_rows,
    ->        COUNT(val) num_vals,
    ->        SUM(val) total,
    ->        MAX(val) max_val,
    ->        AVG(val) avg_val
    -> FROM number_tbl;
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
| 3        | 3        | 9     | 5       | 3.0000  |
+----------+----------+-------+---------+---------+
1 row in set (0.08 sec)
1
2
3
4
5
6
7
8
9
10
11
12

结果正如你所预期的那样：count(*)和count(val)都返回值3，sum(val)返回值9，max(val)返回5，avg(val)返回3。接下来，我将向number_tbl表中添加一个空值，然后再次运行该查询：

mysql> INSERT INTO number_tbl VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT COUNT(*) num_rows,
    ->        COUNT(val) num_vals,
    ->        SUM(val) total,
    ->        MAX(val) max_val,
    ->        AVG(val) avg_val
    -> FROM number_tbl;
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
| 4        | 3        | 9     | 5       | 3.0000  |
+----------+----------+-------+---------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

即便向表中添加了空值，sum()、max()和avg()函数返回的值依然不变，这表明它们会忽略遇到的任何空值。count(*)函数现在返回值4，这是合理的，因为number_tbl表包含四行数据，而count(val)函数仍然返回值3。二者的区别在于，count(*)统计的是行数，而count(val)统计的是val列中包含的值的数量，并且会忽略遇到的任何空值。

# 生成分组

人们很少对查看原始数据感兴趣；相反，从事数据分析的人会希望对原始数据进行处理，以便更好地满足他们的需求。常见的数据处理示例包括：

  • 生成某个地理区域的总计，比如欧洲的总销售额。
  • 找出异常值，例如2005年的顶级销售人员。
  • 确定频率，比如每个分支机构新开设的账户数量。

为了回答这类查询，你需要让数据库服务器根据一个或多个列或表达式对行进行分组。正如你在多个示例中已经看到的，group by子句是在查询中对数据进行分组的机制。在本节中，你将了解如何按一个或多个列对数据进行分组，如何使用表达式对数据进行分组，以及如何在分组内生成汇总（rollups）。

# 单列分组

单列分组是最简单且最常使用的分组类型。例如，如果你想查找每种产品的总余额，你只需按account.product_cd列进行分组，如下所示：

mysql> SELECT product_cd,
    ->        SUM(avail_balance) prod_balance
    -> FROM account
    -> GROUP BY product_cd;
+------------+--------------+
| product_cd | prod_balance |
+------------+--------------+
| BUS        | 9345.55      |
| CD         | 19500.00     |
| CHK        | 73008.01     |
| MM         | 17045.14     |
| SAV        | 1855.76      |
| SBL        | 50000.00     |
+------------+--------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6 rows in set (0.00 sec)

此查询生成六个分组，每种产品对应一个分组，然后对每个分组中的成员的可用余额进行求和。

# 多列分组

在某些情况下，你可能希望生成分组跨越多个列的分组。扩展前面的示例，假设你不仅想查找每种产品的总余额，还想查找产品和分支机构的总余额（例如，沃本分支机构开设的所有支票账户的总余额是多少？）。以下示例展示了如何实现这一点：

mysql> SELECT product_cd,
    ->        open_branch_id,
    ->        SUM(avail_balance) tot_balance
    -> FROM account
    -> GROUP BY product_cd, open_branch_id;
+------------+----------------+-------------+
| product_cd | open_branch_id | tot_balance |
+------------+----------------+-------------+
| BUS        | 2              | 9345.55     |
| BUS        | 4              | 0.00        |
| CD         | 1              | 11500.00    |
| CD         | 2              | 8000.00     |
| CHK        | 1              | 782.16      |
| CHK        | 2              | 3315.77     |
| CHK        | 3              | 1057.75     |
| CHK        | 4              | 67852.33    |
| MM         | 1              | 14832.64    |
| MM         | 3              | 2212.50     |
| SAV        | 1              | 767.77      |
| SAV        | 2              | 700.00      |
| SAV        | 4              | 387.99      |
| SBL        | 3              | 50000.00    |
+------------+----------------+-------------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

此版本的查询生成14个分组，每个分组对应account表中找到的产品和分支机构的每种组合。除了将open_branch_id列添加到select子句中，我还将其添加到group by子句中，因为open_branch_id是从表中检索的，并非通过聚合函数生成的。

# 通过表达式分组

除了使用列对数据进行分组外，你还可以根据表达式生成的值构建分组。考虑以下查询，它根据员工开始在银行工作的年份对员工进行分组：

mysql> SELECT EXTRACT(YEAR FROM start_date) year,
    ->        COUNT(*) how_many
    -> FROM employee
    -> GROUP BY EXTRACT(YEAR FROM start_date);
+------+----------+
| year | how_many |
+------+----------+
| 2004 | 2        |
| 2005 | 3        |
| 2006 | 8        |
| 2007 | 3        |
| 2008 | 2        |
+------+----------+
5 rows in set (0.15 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

此查询采用了一个相当简单的表达式，该表达式使用extract()函数仅返回日期的年份部分，以此对employee表中的行进行分组。

# 生成汇总

在第151页的“多列分组”中，我展示了一个生成每种产品和分支机构的总账户余额的示例。然而，假设除了每种产品/分支机构组合的总余额外，你还想知道每种不同产品的总余额。你可以运行另一个查询并合并结果，可以将查询结果加载到电子表格中，或者可以构建一个Perl脚本、Java程序或其他机制来获取该数据并执行额外的计算。更好的做法是，你可以使用with rollup选项，让数据库服务器为你完成这项工作。以下是在group by子句中使用with rollup的修订查询：

mysql> SELECT product_cd,
    ->        open_branch_id,
    ->        SUM(avail_balance) tot_balance
    -> FROM account
    -> GROUP BY product_cd, open_branch_id WITH ROLLUP;
+------------+----------------+-------------+
| product_cd | open_branch_id | tot_balance |
+------------+----------------+-------------+
| BUS        | 2              | 9345.55     |
| BUS        | 4              | 0.00        |
| BUS        | NULL           | 9345.55     |
| CD         | 1              | 11500.00    |
| CD         | 2              | 8000.00     |
| CD         | NULL           | 19500.00    |
| CHK        | 1              | 782.16      |
| CHK        | 2              | 3315.77     |
| CHK        | 3              | 1057.75     |
| CHK        | 4              | 67852.33    |
| CHK        | NULL           | 73008.01    |
| MM         | 1              | 14832.64    |
| MM         | 3              | 2212.50     |
| MM         | NULL           | 17045.14    |
| SAV        | 1              | 767.77      |
| SAV        | 2              | 700.00      |
| SAV        | 4              | 387.99      |
| SAV        | NULL           | 1855.76     |
| SBL        | 3              | 50000.00    |
| SBL        | NULL           | 50000.00    |
| NULL       | NULL           | 170754.46   |
+------------+----------------+-------------+
21 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

现在结果集中多了七行，六个不同产品各有一行，还有一行是总计（所有产品的总和）。对于六个产品汇总行，open_branch_id列提供了一个空值，因为汇总是在所有分支机构中进行的。例如，查看输出的第三行，你会看到所有分支机构的BUS账户共存入了9345.55美元。对于总计行，product_cd和open_branch_id列都提供了空值；输出的最后一行显示所有产品和分支机构的总计为170754.46美元。

如果你使用的是Oracle数据库，需要使用稍微不同的语法来表示你想要执行汇总操作。使用Oracle时，前面查询的group by子句如下所示：

GROUP BY ROLLUP(product_cd, open_branch_id)
1

这种语法的优点是，它允许你对group by子句中的部分列执行汇总。例如，如果你按列a、b和c进行分组，可以通过以下方式指示服务器仅对b和c执行汇总：

GROUP BY a, ROLLUP(b, c)
1

如果除了计算产品的总计，你还想按分支机构计算总计，那么可以使用with cube选项，它会为分组列的所有可能组合生成汇总行。遗憾的是，MySQL 6.0版本中没有with cube，但SQL Server和Oracle数据库支持该选项。下面是一个使用with cube的示例，但我去掉了mysql>提示符，以表明该查询在MySQL中还无法执行：

SELECT 
    product_cd,
    open_branch_id,
    SUM(avail_balance) tot_balance
FROM account
GROUP BY product_cd, open_branch_id WITH CUBE;
+------------+----------------+-------------+
| product_cd | open_branch_id | tot_balance |
+------------+----------------+-------------+
| NULL       | NULL           | 170754.46   |
| NULL       | 1              | 27882.57    |
| NULL       | 2              | 21361.32    |
| NULL       | 3              | 53270.25    |
| NULL       | 4              | 68240.32    |
| BUS        | 2              | 9345.55     |
| BUS        | 4              | 0.00        |
| BUS        | NULL           | 9345.55     |
| CD         | 1              | 11500.00    |
| CD         | 2              | 8000.00     |
| CD         | NULL           | 19500.00    |
| CHK        | 1              | 782.16      |
| CHK        | 2              | 3315.77     |
| CHK        | 3              | 1057.75     |
| CHK        | 4              | 67852.33    |
| CHK        | NULL           | 73008.01    |
| MM         | 1              | 14832.64    |
| MM         | 3              | 2212.50     |
| MM         | NULL           | 17045.14    |
| SAV        | 1              | 767.77      |
| SAV        | 2              | 700.00      |
| SAV        | 4              | 387.99      |
| SAV        | NULL           | 1855.76     |
| SBL        | 3              | 50000.00    |
| SBL        | NULL           | 50000.00    |
+------------+----------------+-------------+
25 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

使用with cube比使用with rollup版本的查询多生成4行，每个分支机构ID各对应一行。与with rollup类似，product_cd列中会放置空值（NULL），以表明正在进行分支机构汇总。

同样，如果你使用的是Oracle数据库，需要使用略有不同的语法来表示要执行cube操作。在使用Oracle时，上述查询的group by子句如下：

GROUP BY CUBE(product_cd, open_branch_id)
1

# 分组筛选条件

在第4章中，我向你介绍了各种类型的筛选条件，并展示了如何在where子句中使用它们。在对数据进行分组时，你也可以在生成组之后对数据应用筛选条件。having子句就是你应该放置这类筛选条件的地方。考虑以下示例：

mysql> SELECT product_cd, SUM(avail_balance) prod_balance
    -> FROM account
    -> WHERE status = 'ACTIVE'
    -> GROUP BY product_cd
    -> HAVING SUM(avail_balance) >= 10000;
+--------------+---------------+
| product_cd   | prod_balance  |
+--------------+---------------+
| CD           | 19500.00      |
| CHK          | 73008.01      |
| MM           | 17045.14      |
| SBL          | 50000.00      |
+--------------+---------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这个查询有两个筛选条件：一个在where子句中，用于过滤掉非活动账户；另一个在having子句中，用于过滤掉总可用余额少于10000美元的任何产品。因此，其中一个筛选条件在数据分组之前起作用，而另一个筛选条件在创建组之后对数据起作用。如果你错误地将两个筛选条件都放在where子句中，将会看到以下错误：

mysql> SELECT product_cd, SUM(avail_balance) prod_balance
    -> FROM account
    -> WHERE status = 'ACTIVE'
    ->     AND SUM(avail_balance) > 10000
    -> GROUP BY product_cd;
ERROR 1111 (HY000): Invalid use of group function
1
2
3
4
5
6

这个查询失败是因为不能在查询的where子句中包含聚合函数。这是因为where子句中的筛选条件是在分组之前进行评估的，所以服务器还无法对组执行任何函数。

在向包含group by子句的查询中添加筛选条件时，要仔细考虑该筛选条件是作用于原始数据（在这种情况下应放在where子句中），还是作用于分组数据（在这种情况下应放在having子句中）。

不过，你可以在having子句中包含未出现在select子句中的聚合函数，如下所示：

mysql> SELECT product_cd, SUM(avail_balance) prod_balance
    -> FROM account
    -> WHERE status = 'ACTIVE'
    -> GROUP BY product_cd
    -> HAVING MIN(avail_balance) >= 1000
    ->     AND MAX(avail_balance) <= 10000;
+--------------+---------------+
| product_cd   | prod_balance  |
+--------------+---------------+
| CD           | 19500.00      |
| MM           | 17045.14      |
+--------------+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

这个查询为每个活动产品生成总余额，但随后having子句中的筛选条件排除了最小余额小于1000美元或最大余额大于10000美元的所有产品。

# 知识测验

完成以下练习，测试你对SQL分组和聚合功能的掌握程度。可参考附录C中的答案核对你的作业。

# 练习8 - 1

构建一个查询，统计account表中的行数。

# 练习8 - 2

修改练习8 - 1中的查询，统计每个客户持有的账户数量。显示客户ID和每个客户的账户数量。

# 练习8 - 3

修改练习8 - 2中的查询，只包含拥有至少两个账户的客户。

# 练习8 - 4（附加题）

按产品和分支机构查找总可用余额，条件是每个产品和分支机构的账户数量超过一个。按总余额从高到低对结果进行排序。

第7章 数据生成、转换与操作
第9章 子查询

← 第9章 子查询→

最近更新
01
C++语言面试问题集锦 目录与说明
03-27
02
第四章 Lambda函数
03-27
03
第二章 关键字static及其不同用法
03-27
更多文章>
Copyright © 2024-2025 沪ICP备2023015129号 张小方 版权所有