读书笔记--SQL必知必会10--分组数据

10.1 数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

10.2 创建分组

使用SELECT语句的GROUP BY子句建立分组。

• GROUP BY子句必须出现在WHERE之后，ORDER BY子句之前。
• GROUP BY子句可以包含任意数目的列，可以对分组进行嵌套。
• 如果GUOUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。
• GUOUP BY子句中的列必须是实际有效的检索列或表达式，不能使用别名。
• 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
• 一行或多行NULL值，将作为一个分组返回。

某些DBMS允许根据SELECT列表中的相对位置指定列，但不建议使用，容易导致编辑SQL语句时出错。

MariaDB [sqlbzbh]> SELECT * FROM Products;+---------+---------+---------------------+------------+-----------------------------------------------------------------------+| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |+---------+---------+---------------------+------------+-----------------------------------------------------------------------+| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it || BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              || BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      || BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          || BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         || BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         || RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              || RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       || RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |+---------+---------+---------------------+------------+-----------------------------------------------------------------------+9 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;+---------+-----------+| vend_id | num_prods |+---------+-----------+| BRS01   |         3 || DLL01   |         4 || FNG01   |         2 |+---------+-----------+3 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY 1;+---------+-----------+| vend_id | num_prods |+---------+-----------+| BRS01   |         3 || DLL01   |         4 || FNG01   |         2 |+---------+-----------+3 rows in set (0.00 sec)MariaDB [sqlbzbh]>

10.3 过滤分组

使用HAVING子句可以过滤分组。
相比之下，WHERE子句过滤指定的是行而不是分组。也可以理解为: WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
HAVING子句支持所有WHERE操作符，也就是说有关WHERE的句法都适用于HAVING，只是关键字可能有差别。

MariaDB [sqlbzbh]> SELECT * FROM Orders;+-----------+---------------------+------------+| order_num | order_date          | cust_id    |+-----------+---------------------+------------+|     20005 | 2012-05-01 00:00:00 | 1000000001 ||     20006 | 2012-01-12 00:00:00 | 1000000003 ||     20007 | 2012-01-30 00:00:00 | 1000000004 ||     20008 | 2012-02-03 00:00:00 | 1000000005 ||     20009 | 2012-02-08 00:00:00 | 1000000001 |+-----------+---------------------+------------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id;+------------+--------+| cust_id    | orders |+------------+--------+| 1000000001 |      2 || 1000000003 |      1 || 1000000004 |      1 || 1000000005 |      1 |+------------+--------+4 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >=2;+------------+--------+| cust_id    | orders |+------------+--------+| 1000000001 |      2 |+------------+--------+1 row in set (0.00 sec)MariaDB [sqlbzbh]> 

同时使用WHERE子句和HAVING子句

MariaDB [sqlbzbh]> SELECT * FROM Products;+---------+---------+---------------------+------------+-----------------------------------------------------------------------+| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |+---------+---------+---------------------+------------+-----------------------------------------------------------------------+| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it || BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              || BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      || BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          || BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         || BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         || RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              || RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       || RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |+---------+---------+---------------------+------------+-----------------------------------------------------------------------+9 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id HAVING COUNT(*) >=2;+---------+-----------+| vend_id | num_prods |+---------+-----------+| BRS01   |         3 || DLL01   |         4 || FNG01   |         2 |+---------+-----------+3 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >=2;+---------+-----------+| vend_id | num_prods |+---------+-----------+| BRS01   |         3 || FNG01   |         2 |+---------+-----------+2 rows in set (0.00 sec)MariaDB [sqlbzbh]> 

10.4 分组和排序

对比GROUP BY 与 ORDER BY

GROUP BYOREDR BY输出可能不是分组的顺序对产生的输出排序只能使用选择列或表达式列，而且必须使用每个选择列表达式任意列都可以使用，包括非选择列必须使用与聚集函数一起的列或表达式不一定需要

GROUP BY子句的分组数据输出并不一定是顺序的，输出的顺序也不一定满足实际的需要，所以千万不要仅仅依赖GROUP BY排序数据。
一般在使用GROUP BY子句的同时也使用ORDER BY子句，保证数据正确排序，满足实际需要。

MariaDB [sqlbzbh]> SELECT * FROM OrderItems;+-----------+------------+---------+----------+------------+| order_num | order_item | prod_id | quantity | item_price |+-----------+------------+---------+----------+------------+|     20005 |          1 | BR01    |      100 |       5.49 ||     20005 |          2 | BR03    |      100 |      10.99 ||     20006 |          1 | BR01    |       20 |       5.99 ||     20006 |          2 | BR02    |       10 |       8.99 ||     20006 |          3 | BR03    |       10 |      11.99 ||     20007 |          1 | BR03    |       50 |      11.49 ||     20007 |          2 | BNBG01  |      100 |       2.99 ||     20007 |          3 | BNBG02  |      100 |       2.99 ||     20007 |          4 | BNBG03  |      100 |       2.99 ||     20007 |          5 | RGAN01  |       50 |       4.49 ||     20008 |          1 | RGAN01  |        5 |       4.99 ||     20008 |          2 | BR03    |        5 |      11.99 ||     20008 |          3 | BNBG01  |       10 |       3.49 ||     20008 |          4 | BNBG02  |       10 |       3.49 ||     20008 |          5 | BNBG03  |       10 |       3.49 ||     20009 |          1 | BNBG01  |      250 |       2.49 ||     20009 |          2 | BNBG02  |      250 |       2.49 ||     20009 |          3 | BNBG03  |      250 |       2.49 |+-----------+------------+---------+----------+------------+18 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num;+-----------+-------+| order_num | items |+-----------+-------+|     20005 |     2 ||     20006 |     3 ||     20007 |     5 ||     20008 |     5 ||     20009 |     3 |+-----------+-------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;+-----------+-------+| order_num | items |+-----------+-------+|     20006 |     3 ||     20007 |     5 ||     20008 |     5 ||     20009 |     3 |+-----------+-------+4 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;+-----------+-------+| order_num | items |+-----------+-------+|     20006 |     3 ||     20009 |     3 ||     20007 |     5 ||     20008 |     5 |+-----------+-------+4 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items;+-----------+-------+| order_num | items |+-----------+-------+|     20006 |     3 ||     20009 |     3 ||     20007 |     5 ||     20008 |     5 |+-----------+-------+4 rows in set (0.01 sec)MariaDB [sqlbzbh]> 

10.5 SELECT子句顺序

在SELECT语句中使用子句时，必须遵循的次序。

子句说明是否必须使用SELECT要返回的列或表达式是FROM从表中检索数据仅在从表选择数据时使用WHERE行过滤否GROUP BY分组仅在按组计算聚集时使用HAVING组过滤否ORDER BY输出排序否