sql 对表进行聚合查询的方法_MySQL两张表联合查询SQL语句

学习重点

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

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

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

一、聚合函数

通过 SQL 对数据进行某种操作或计算时需要使用函数。例如，计算表中全部数据的行数时，可以使用 COUNT 函数。该函数就是使用 COUNT（计数）来命名的。除此之外，SQL 中还有很多其他用于汇总的函数，请大家先记住以下 5 个常用的函数。

KEYWORD

• 函数

• COUNT 函数

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

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

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

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

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

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

KEYWORD

• 聚合函数

• 聚集函数

• 聚合

接下来，本文将继续使用在 表的创建 中创建的 Product 表（图 1）来学习函数的使用方法。

二、计算表中数据的行数

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

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

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

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

执行结果

COUNT() 中的星号，我们在 SELECT 语句基础 中已经学过，代表全部列的意思。COUNT 函数的输入值就记述在其后的括号中。

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

KEYWORD

• 参数（parameter）

• 返回值

三、计算 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;

执行结果

) 的结果并不相同” loading=”lazy”>

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

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

法则 1

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

四、计算合计值

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

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

KEYWORD

• SUM 函数

代码清单 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，也可以计算出合计值。还记得 算术运算符和比较运算符 中内容的读者可能会产生如下疑问。

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

有这样疑问的读者思维很敏锐，但实际上这两者并不矛盾。从结果上说，所有的聚合函数，如果以列名为参数，那么在计算之前就已经把 NULL 排除在外了。因此，无论有多少个 NULL 都会被无视。这与“等价为 0”并不相同 ^[2]。

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

法则 2

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

五、计算平均值

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

KEYWORD

• AVG 函数

代码清单 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 进行计算。
但是有时也想将 NULL 作为 0 进行计算，具体的实现方式请参 各种各样的函数。

六、计算最大值和最小值

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

KEYWORD

• MAX 函数

• MIN 函数

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

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

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

执行结果

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

但是，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 函数只适用于数值类型的列。

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

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

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

例如，拿商品种类来说，表中总共有 3 种商品共 8 行数据，其中衣服 2 行，办公用品 2 行，厨房用具 4 行。如果想要计算出商品种类的个数，怎么做比较好呢？删除重复数据然后再计算数据行数似乎是个不错的办法。实际上，在使用 COUNT 函数时，将 SELECT 语句基础 中介绍过的 DISTINCT 关键字作为参数，就能得到我们想要的结果了（代码清单 10）。

KEYWORD

• DISTINCT 关键字

代码清单 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，可以删除重复数据。

请参阅

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

（完）