SQL——汇总分组排序

对表进行聚合分组

SELECT+聚合函数+GROUP BY：
SELECT 列名称1,列名称2,… FROM 表名称 GROUP BY 列名称1,列名称2,…
注：GROUP BY子句后指定的列——聚合键（分组列）

mysql> -- 对商品种类进行分组
mysql> select product_type from product_details group by product_type;
+--------------+
| product_type |
+--------------+
| 衣服         |
| 办公用品     |
| 厨房用具     |
+--------------+
3 rows in set (0.00 sec)

mysql> -- 对商品种类进行分组并且分类统计
mysql> select product_type,count(*) from product_details group by product_type;
+--------------+----------+
| product_type | count(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)

聚合函数+GROUP BY:
SELECT 列名称，COUNT/AVG/SUM/MAX/MIN FROM 表名称 GROUP BY 列名称;
注：一列数据中包含NULL时，count（*）和count(列名称)在统计NULL时会有差异。

mysql> select purchase_price,count(purchase_price) from product_details group by purchase_price;
+----------------+-----------------------+
| purchase_price | count(purchase_price) |
+----------------+-----------------------+
|            500 |                     1 |
|            320 |                     1 |
|           2800 |                     2 |
|           5000 |                     1 |
|           NULL |                     0 |
|            790 |                     1 |
+----------------+-----------------------+
6 rows in set (0.00 sec)

mysql> select purchase_price,count(*) from product_details 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)

条件筛选后再对表聚合分组

SELECT 列名称1,列名称2,… FROM 表名称 WHERE 条件表达式 GROUP BY 列名称1,列名称2,…;
SQL子句书写顺序：SELECT——FROM——WHERE——GROUP BY
DBMAS内部执行顺序：FROM——WHERE——GROUP BY——SELECT

mysql> select sale_price,count(*) from product_details