2019-08-06
聚集函数(aggregate function):运行在行组上,计算和返回单个值的函数。
SQL
聚集函数
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值,忽略NULL 值 |
COUNT() | 返回某列的行数,忽略NULL 值,但使用通配符* 时不忽略 |
MAX() | 返回某列的最大值,忽略NULL 值 |
MIN() | 返回某列的最小值,忽略NULL 值 |
SUM() | 返回某列值之和,忽略NULL 值 |
以上五个聚集函数都可以如下使用:
- 对所有的行执行计算,指定
ALL
参数或不给参数(因为ALL
是默认行为); - 只包含不同的值,指定
DISTINCT
参数。
注意:
- 如果指定列名,则
DISTINCT
只能用于COUNT()
。DISTINCT
不能用于COUNT(*)
,因此不允许使用COUNT(DISTINCT *)
,否则会产生错误。类似地,DISTINCT
必须使用列名,不能用于计算或表达式。- 虽然
DISTINCT
从技术上可用于MIN()
和MAX()
,但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。
mysql> select cust_country from customers;
+--------------+
| cust_country |
+--------------+
| USA |
| USA |
| USA |
| USA |
| USA |
+--------------+
5 rows in set (0.00 sec)
mysql> select count(*) from customers where cust_country = 'USA';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(DISTINCT *) from customers where cust_country = 'USA';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '*) from customers where cust_country = 'USA'' at line 1
mysql> select count(DISTINCT cust_country) from customers where cust_country = 'USA';
+------------------------------+
| count(DISTINCT cust_country) |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.07 sec)
1. AVG()
函数
AVG()
只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个列的平均值,必须使用多个AVG()
函数。NULL
值,AVG()
函数忽略列值为NULL
的行
2. MAX()
函数和MIN()
对非数值数据使用MAX()
和MIN()
虽然
MAX()
一般用来找出最大的数值或日期值,但MySQL
允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。在用于文本数据时,如果数据按相应的列升序排序,则MAX()
返回最后一行,MIN()
返回最前面的行;如果数据按相应的列降序排序,则MAX()
返回最前面的行,MIN()
返回最后一行。
mysql> SELECT cust_name
-> FROM customers;
+----------------+
| cust_name |
+----------------+
| Coyote Inc. |
| Mouse House |
| Wascals |
| Yosemite Place |
| E Fudd |
+----------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_name
-> FROM customers
-> ORDER BY cust_name;
+----------------+
| cust_name |
+----------------+
| Coyote Inc. |
| E Fudd |
| Mouse House |
| Wascals |
| Yosemite Place |
+----------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_name
-> FROM customers
-> ORDER BY cust_name DESC;
+----------------+
| cust_name |
+----------------+
| Yosemite Place |
| Wascals |
| Mouse House |
| E Fudd |
| Coyote Inc. |
+----------------+
5 rows in set (0.00 sec)
mysql> SELECT MAX(cust_name)
-> FROM customers
-> ORDER BY cust_name;
+----------------+
| MAX(cust_name) |
+----------------+
| Yosemite Place |
+----------------+
1 row in set (0.07 sec)
mysql> SELECT MAX(cust_name)
-> FROM customers
-> ORDER BY cust_name DESC;
+----------------+
| MAX(cust_name) |
+----------------+
| Yosemite Place |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(cust_name)
-> FROM customers
-> ORDER BY cust_name;
+----------------+
| MIN(cust_name) |
+----------------+
| Coyote Inc. |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(cust_name)
-> FROM customers
-> ORDER BY cust_name DESC;
+----------------+
| MIN(cust_name) |
+----------------+
| Coyote Inc. |
+----------------+
1 row in set (0.00 sec)
3. SUM()
函数
mysql> SELECT SUM(quantity) AS items_ordered
-> FROM orderitems
-> WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
1 row in set (0.07 sec)
mysql># 利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算
mysql> SELECT SUM(item_price * quantity) AS total_price
-> FROM orderitems
-> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
1 row in set (0.00 sec)
4. 组合聚集函数
mysql> SELECT COUNT(*) AS num_items,
-> MIN(prod_price) AS price_min,
-> MAX(prod_price) AS price_max,
-> AVG(prod_price) AS price_avg
-> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)