# 聚合排序

## 聚合函数

```
COUNT SUM  AVG  MAX  MIN
```

## 统计数据量

```
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

#COUNT(*)，包含NULL的
mysql> SELECT COUNT(*) FROM Product;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

#COUNT(具体列)，不计算NULL的
mysql> SELECT COUNT(purchase_price) FROM Product;
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)
```

## AVG、SUM

不算NULL的

```
mysql> SELECT AVG(purchase_price) FROM Product;
+---------------------+
| AVG(purchase_price) |
+---------------------+
|           2035.0000 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(purchase_price) FROM Product;
+---------------------+
| SUM(purchase_price) |
+---------------------+
|               12210 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(purchase_price)/6 FROM Product;
+-----------------------+
| SUM(purchase_price)/6 |
+-----------------------+
|             2035.0000 |
+-----------------------+
1 row in set (0.00 sec)


mysql> SELECT SUM(sale_price), SUM(DISTINCT sale_price) FROM Product;
+-----------------+--------------------------+
| SUM(sale_price) | SUM(DISTINCT sale_price) |
+-----------------+--------------------------+
|           16780 |                    16280 |
+-----------------+--------------------------+
1 row in set (0.00 sec)
```

## 表分组、GROUP BY

```
SELECT <col1>, <col2>, <col3>, ... FROM <tabname>
GROUP BY <col1>, <col2>, <col3>, ...;


mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

mysql> SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)


#有NULL时，也看做一组数据
mysql> SELECT purchase_price, COUNT(*) FROM Product
    -> GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|            320 |        1 |
|           2800 |        2 |
|           5000 |        1 |
|           NULL |        2 |
|            790 |        1 |
+----------------+----------+
6 rows in set (0.00 sec)
```

## WHERE GROUP BY

```
SELECT <col1>, <col2>, <col3>, ... FROM <tbname>
WHERE
GROUP BY <col1>, <col2>, <col3>, ...;

#衣服里面，不同价格的数量
mysql> SELECT purchase_price, COUNT(*) FROM Product
    -> WHERE product_type = '衣服' GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.00 sec)
```

PS：无法把聚合键以外的列，写在SELECT列中，显然。

PS：不能在聚合键中写SELECT中AS的别名，因为`先GROUP BY执行才有SELECT中`的别名创建；

PS：WHERE中不能使用聚合函数；

```
SELECT product_type, COUNT(*) 
FROM Product
WHERE COUNT(*) = 2 # 不行
GROUP BY product_type;
```

## HAVING，为聚合指定条件

```
SELECT <col1>, <col2>, <col3>, ...
FROM <tbname>
GROUP BY <col1>, <col2>, <col3>, ...
HAVING <条件>


# 不加HAVING 时的product_type分组
mysql> SELECT product_type, COUNT(*) FROM Product
    -> GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)

#查询 产品类型，要求类型数=2
mysql> SELECT product_type, COUNT(*) FROM Product
    -> GROUP BY product_type HAVING COUNT(*) = 2;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
+--------------+----------+
2 rows in set (0.00 sec)
```

## HAVING中使用聚合函数

PS：HAVING 使用时，GROUP BY已经分组完成，因此HAVING可以使用任何SELECT中的函数，相当于对分组的结果当作表

```
#不过滤时的product_type分组平均
mysql> SELECT product_type, AVG(sale_price) FROM Product
    -> GROUP BY product_type;

+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 办公用品     |        300.0000 |
| 厨房用具     |       2795.0000 |
+--------------+-----------------+
3 rows in set (0.01 sec)

#过滤要求>2400时的product_type分组平均
mysql> SELECT  product_type, AVG(sale_price) FROM Product
    -> GROUP BY product_type
    -> HAVING AVG(sale_price)>2400;
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 厨房用具     |       2795.0000 |
+--------------+-----------------+
2 rows in set (0.00 sec)
```

PS：HAVING能使用：`常数、聚合函数、GROUP BY中的列`，

错误，GROUP BY后不存在product\_name数据列：

```
SELECT product_type, COUNT(*) FROM Product
GROUP BY product_type
HAVING product_name = '􏶀􏶁􏶂';
```

PS：`HAVING通常对GROUP BY`后的数据进行操作，而之前的可以用WHERE操作，虽然有时候两者可以互用；

## ORDER BY

```
# DESC降序
mysql> 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 |           NULL |
| 0008       | 圆珠笔       |        100 |           NULL |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)


# ASC升序
mysql> SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price ASC;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 圆珠笔       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0007       | 擦菜板       |        880 |            790 |
| 0001       | T恤          |       1000 |            500 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0005       | 高压锅       |       6800 |           5000 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
```

PS:可以多个键排序`ORDER BY sale_price, product_id`;

PS:`GROUP BY中不能使用别名`，但是`ORDER BY可以`，因为`此时别名已经创建`.

```
#执行顺序
FROM > WHERE  > GROUP BY > HAVING >SELECT> ORDER BY 

# PS:显然，ORDER BY可以使用SELECT中的列，包括函数
mysql> SELECT product_type, COUNT(*) FROM Product
    -> GROUP BY product_type ORDER BY COUNT(*);
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT product_type, COUNT(*) FROM Product GROUP BY product_type ORDER BY COUNT(*) DESC;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 厨房用具     |        4 |
| 衣服         |        2 |
| 办公用品     |        2 |
+--------------+----------+
3 rows in set (0.00 sec)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/juhe-paixu.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.