17、MySQL的数据分组

group by

使用了group by， 就不必指定要计算和估值的每个组了、系统会自动生成
group by 必须出现在where字句之后，order by字句之前
1、创建分组：vend_id为描述字段，不能为计算字段，后面加上计算字段，group by后必须加上所有的描述字段

mysql> select vend_id, count(*) from products group by vend_id;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
+---------+----------+

2、创建摘要with rollup

mysql> select vend_id, count(*) from products group by vend_id with rollup;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
| NULL    |       14 |
+---------+----------+

3、分组过滤器 having

mysql> select vend_id, count(*) from products group by vend_id with rollup having count(*) > 2;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
|    1001 |        3 |
|    1003 |        7 |
| NULL    |       14 |
+---------+----------+

4、分组和排序

mysql> select order_num, sum(quantity * item_price) from orderitems group by order_num;
+-----------+----------------------------+
| order_num | sum(quantity * item_price) |
+-----------+----------------------------+
|     20005 | 149.87                     |
|     20006 | 55.00                      |
|     20007 | 1000.00                    |
|     20008 | 125.00                     |
|     20009 | 38.47                      |
+-----------+----------------------------+

mysql> select order_num, sum(quantity * item_price) from orderitems group by order_num order by sum(quantity * item_price);
+-----------+----------------------------+
| order_num | sum(quantity * item_price) |
+-----------+----------------------------+
|     20009 | 38.47                      |
|     20006 | 55.00                      |
|     20008 | 125.00                     |
|     20005 | 149.87                     |
|     20007 | 1000.00                    |
+-----------+----------------------------+

mysql> select order_num, sum(quantity * item_price) from orderitems group by order_num having sum(quantity * item_price) > 100 order by sum(quantity * item_price);
+-----------+----------------------------+
| order_num | sum(quantity * item_price) |
+-----------+----------------------------+
|     20008 | 125.00                     |
|     20005 | 149.87                     |
|     20007 | 1000.00                    |
+-----------+----------------------------+

逆序+desc

mysql> select order_num, sum(quantity * item_price) as title from orderitems group by order_num having title > 100 order by title desc;
+-----------+---------+
| order_num | title   |
+-----------+---------+
|     20007 | 1000.00 |
|     20005 | 149.87  |
|     20008 | 125.00  |
+-----------+---------+

分组函数的执行顺序

select–>>from–>>where–>>group by–>>having–>>order by