MySQL学习足迹记录11--分组数据--GROUP BY，HAVING

快来打我* 2022-01-11 08:41 232阅读 0赞

**1.创建分组GROUP BY  
先列出所有的vend\_id，以便作对比  
 mysql> SELECT vend\_id FROM products;**   
\+---------+  
| vend\_id |  
\+---------+  
| 1001 |  
| 1001 |  
| 1001 |  
| 1002 |  
| 1002 |  
| 1003 |  
| 1003 |  
| 1003 |  
| 1003 |  
| 1003 |  
| 1003 |  
| 1003 |  
| 1005 |  
| 1005 |  
\+---------+  
14 rows in set (0.00 sec)  
  
  
  
  
 **用GROUP BY进行分组  
mysql> SELECT vend\_id,COUNT(\*) AS num\_prods  
 \-> FROM products  \#先分组，再分别计算COUNT（\*）  
 \-> GROUP BY vend\_id;**   
\+---------+-----------+  
| vend\_id | num\_prods |  
\+---------+-----------+  
| 1001 | 3 |  
| 1002 | 2 |  
| 1003 | 7 |  
| 1005 | 2 |  
\+---------+-----------+  
4 rows in set (0.00 sec)  
  
  
  
  
**TIPS：  
\*如果列中有多行NULL值，它们将分为一组  
\*GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前  
  
  
  
  
2.过滤分组HAVING  
\*HAVING 与 WHERE 的区别：  
WHERE过滤行，而HAVING过滤分组  
  
eg:  
 mysql> SELECT vend\_id,COUNT(\*) AS num\_prods   
 \-> FROM products  
 \-> GROUP BY vend\_id  
 \-> HAVING COUNT(\*)>2; \#从结果中过滤不符合COUNT(\*)>2的组**   
\+---------+-----------+  
| vend\_id | num\_prods |  
\+---------+-----------+  
| 1001 | 3 |  
| 1003 | 7 |  
\+---------+-----------+  
2 rows in set (0.00 sec)  
  
  
 **\* WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤，所以，WHERE排除的行不包括在分组中  
eg:  
先列出原始数据作对比  
mysql> SELECT vend\_id,prod\_price FROM products  
 \-> ORDER BY prod\_price;**   
\+---------+------------+  
| vend\_id | prod\_price |  
\+---------+------------+  
| 1003 | 2.50 |  
| 1003 | 2.50 |  
| 1002 | 3.42 |  
| 1003 | 4.49 |  
| 1001 | 5.99 |  
| 1002 | 8.99 |  
| 1001 | 9.99 |  
| 1003 | 10.00 |  
| 1003 | 10.00 |  
| 1003 | 13.00 |  
| 1001 | 14.99 |  
| 1005 | 35.00 |  
| 1003 | 50.00 |  
| 1005 | 55.00 |  
\+---------+------------+  
14 rows in set (0.00 sec )  
  
  
  
  
  
  
**mysql> SELECT vend\_id,COUNT(\*) AS num\_prods  
 \-> FROM products**

**-> WHERE prod\_price >14 \#WHERE过滤后只剩下上表中最后3条记录，**

**-> GROUP BY vend\_id  \#HAVING再过滤分组后vend\_id为不符合COUNT(\*) >=2组**

**->  HAVING COUNT(\*) >=2;**  
\+---------+-----------+  
| vend\_id | num\_prods |  
\+---------+-----------+  
| 1005 | 2 |  
\+---------+-----------+  
1 row in set (0.00 sec)   
  
  
  
  
  
  
**3. 分组和排序  
GROUP BY和ORDER BY的区别  
\*ORDER BY指定的条件可以是任意列  
\*GROUP BY指定的条件只可能使用选择列或列表达式  
  
  
TIPS：  
一般在使用GROUP BY子句时，也应该给出ORDER BY子句  
Examples:  
先列出原始数据：  
 mysql> SELECT order\_num,quantity,item\_price FROM orderitems;**  
\+-----------+----------+------------+  
| order\_num | quantity | item\_price |  
\+-----------+----------+------------+  
| 20005 | 10 | 5.99 |  
| 20005 | 3 | 9.99 |  
| 20005 | 5 | 10.00 |  
| 20005 | 1 | 10.00 |  
| 20006 | 1 | 55.00 |  
| 20007 | 100 | 10.00 |  
| 20008 | 50 | 2.50 |  
| 20009 | 1 | 10.00 |  
| 20009 | 1 | 8.99 |  
| 20009 | 1 | 4.49 |  
| 20009 | 1 | 14.99 |  
\+-----------+----------+------------+  
11 rows in set (0.00 sec)  
  
  
  
  
  
  
**mysql> SELECT order\_num,SUM(quantity\*item\_price) AS ordertotal  
 \-> FROM orderitems  
 \-> GROUP BY order\_num  
 \-> HAVING SUM(quantity\*item\_price) >= 50;  
\+-----------+------------+   \#未用ORDERBY指定排序，结果可能不是想要的，例如按ordertotal升序**   
 | order\_num | ordertotal |  
\+-----------+------------+  
| 20005 | 149.87 |  
| 20006 | 55.00 |  
| 20007 | 1000.00 |  
| 20008 | 125.00 |  
\+-----------+------------+  
4 rows in set (0.00 sec)  
  
  
  
  
**mysql> SELECT order\_num,SUM(quantity\*item\_price) AS ordertotal  
 \-> FROM orderitems   
 \-> GROUP BY order\_num   
 \-> HAVING SUM(quantity\*item\_price) >=50  
 \-> ORDER BY ordertotal;  \# 用ORDERBY指定排序方式**  
\+-----------+------------+  
| order\_num | ordertotal |  
\+-----------+------------+  
| 20006 | 55.00 |  
| 20008 | 125.00 |  
| 20005 | 149.87 |  
| 20007 | 1000.00 |  
\+-----------+------------+  
4 rows in set (0.01 sec)